Re: a JOIN on same table, but 'slided over'

["Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com>]

On 6/26/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

"news.gmane.org" <nis(at)superlativ(dot)dk> writes:
> Gurjeet Singh skrev:
>> Also note that this query is much cheaper that the 'distinct on' query
>> by more than two orders on magnitude ( 217.86 vs. 98040.67):

> No it isn't. The estimate is much lower, but the actual times are very
> close:

> [explain of distinct on]
>> Time: 5.003 ms

> [explain of correlated subquery]
>> Time: 4.125 ms

You're both confused:

???

the planner estimate certainly should not be taken
as gospel,

true

but the actual runtime of an EXPLAIN (not EXPLAIN ANALYZE)
only reflects planning effort.

Agree completely

EXPLAIN ANALYZE output would be a lot more suitable to settle the
question which one is faster.

Agree again. I was using the EXPLAIN output just to make a point that optimizer thinks the query utilizing a subquery is much cheaper (and hence maybe faster) than the 'distinct on' query.

In a later mail I posted the analyze o/p too...

-- 
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

17°29'34.37"N  78°30'59.76
"E - Hyderabad *
18°32'57.25"N  73°56'25.42"E - Pune

Sent from my BlackLaptop device