Skip site navigation (1) Skip section navigation (2)

Re: Any better plan for this query?..

From: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
To: Dave Dutcher <dave(at)tridecap(dot)com>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Any better plan for this query?..
Date: 2009-05-18 22:32:43
Message-ID: 5482c80a0905181532kc1bef8nbd4aee8aa8db9ee@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
>
>

In response to

Responses

pgsql-performance by date

Next:From: DimitriDate: 2009-05-18 22:32:54
Subject: Re: Any better plan for this query?..
Previous:From: Dave DutcherDate: 2009-05-18 19:44:34
Subject: Re: Any better plan for this query?..

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group