Re: Any better plan for this query?..

Thanks Dave for correction, but I'm also curious where the time is
wasted in this case?..

0.84ms is displayed by "psql" once the result output is printed, and I
got similar time within my client (using libpq) which is not printing
any output..

Rgds,
-Dimitri

On 5/18/09, Dave Dutcher <dave(at)tridecap(dot)com> wrote:
>
> What I don't understand is the part where you talking about disabling hash
> joins:
>
>> * result: planner replaced hash join is replaced by merge join
>> * execution time: 0.84ms !
>> * NOTE: curiously planner is expecting to execute this query in 0.29ms
> - so it's supposed from its logic to be faster, so why this plan is not used
> from the beginning???...
>>
>> Sort (cost=4562.83..4568.66 rows=2329 width=176) (actual
> time=0.237..0.237 rows=20 loops=1)
>> Sort Key: h.horder
>> Sort Method: quicksort Memory: 30kB
>> -> Merge Join (cost=4345.89..4432.58 rows=2329 width=176)
> (actual time=0.065..0.216 rows=20 loops=1)
>> Merge Cond: (s.ref = h.ref_stat)
>> -> Index Scan using stat_ref_idx on stat s
> (cost=0.00..49.25 rows=1000 width=45) (actual time=0.018..0.089 rows=193
> loops=1)
>> -> Sort (cost=4345.89..4351.72 rows=2329 width=135)
> (actual time=0.042..0.043 rows=20 loops=1)
>> Sort Key: h.ref_stat
>> Sort Method: quicksort Memory: 30kB
>> -> Index Scan using history_ref_idx on history h
> (cost=0.00..4215.64 rows=2329 width=135) (actual time=0.012..0.025 rows=20
> loops=1)
>> Index Cond: (ref_object = '0000000001'::bpchar)
>> Total runtime: 0.288 ms
>> (12 rows)
>
> The explain analyze ran the query in 0.288 ms. That is the actual time it
> took to run the query on the server. It is not an estimate of the time.
> You measured 0.84 ms to run the query, which seems to imply either a problem
> in one of the timing methods or that 66% of your query execution time is
> sending the results to the client. I'm curious how you did you execution
> time measurements.
>
> Dave
>
>