pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSlow query-plan generation (fast query) PG 9.2
4+ messages / 4 participants
[nested] [flat]
* Slow query-plan generation (fast query) PG 9.2
@ 2013-09-03 07:46 jesper@krogh.cc
2013-09-03 07:47 ` Re: Slow query-plan generation (fast query) PG 9.2 Craig Ringer <craig@2ndquadrant.com>
0 siblings, 1 reply; 4+ messages in thread
From: jesper@krogh.cc @ 2013-09-03 07:46 UTC (permalink / raw)
To: pgsql-performance
Hi.
I have a strange situation where generating the query plan takes 6s+ and
executing it takes very little time.
2013-09-03 09:19:38.726 db=# explain select table.id from db.table left
join db.tablepro on db.id = tablepro.table_id where table.fts @@
to_tsquery('english','q12345') ;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=43.71..12711.39 rows=2930 width=4)
-> Bitmap Heap Scan on sequence (cost=43.71..4449.10 rows=2930 width=4)
Recheck Cond: (fts @@ '''q12345'''::tsquery)
-> Bitmap Index Scan on table_gin_idx (cost=0.00..42.98
rows=2930 width=0)
Index Cond: (fts @@ '''q12345'''::tsquery)
-> Index Only Scan using tablepro_seqid_idx on tablepro
(cost=0.00..2.81 rows=1 width=4)
Index Cond: (tablepro_id = table.id)
(7 rows)
Time: 10458.404 ms
The query gives 4 rows out of 50.000.000, so the query-plan is actually
correct and as expected.
Any suggestions?
Jesper
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow query-plan generation (fast query) PG 9.2
2013-09-03 07:46 Slow query-plan generation (fast query) PG 9.2 jesper@krogh.cc
@ 2013-09-03 07:47 ` Craig Ringer <craig@2ndquadrant.com>
2013-09-03 19:34 ` Re: Slow query-plan generation (fast query) PG 9.2 Jesper Krogh <jesper@krogh.cc>
0 siblings, 1 reply; 4+ messages in thread
From: Craig Ringer @ 2013-09-03 07:47 UTC (permalink / raw)
To: pgsql-performance
On 09/03/2013 03:46 PM, jesper@krogh.cc wrote:
> Hi.
>
> I have a strange situation where generating the query plan takes 6s+ and
> executing it takes very little time.
How do you determine that it's planning time at fault here?
Please take separate timing for:
PREPARE testq AS select table.id from db.table left
join db.tablepro on db.id = tablepro.table_id where table.fts @@
to_tsquery('english','q12345') ;
and then:
EXPLAIN ANALYZE EXECUTE testq;
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow query-plan generation (fast query) PG 9.2
2013-09-03 07:46 Slow query-plan generation (fast query) PG 9.2 jesper@krogh.cc
2013-09-03 07:47 ` Re: Slow query-plan generation (fast query) PG 9.2 Craig Ringer <craig@2ndquadrant.com>
@ 2013-09-03 19:34 ` Jesper Krogh <jesper@krogh.cc>
2013-09-04 13:59 ` Re: Slow query-plan generation (fast query) PG 9.2 Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jesper Krogh @ 2013-09-03 19:34 UTC (permalink / raw)
To: Craig Ringer <craig@2ndquadrant.com>; +Cc: pgsql-performance
On 03/09/13 09:47, Craig Ringer wrote:
> On 09/03/2013 03:46 PM, jesper@krogh.cc wrote:
>> Hi.
>>
>> I have a strange situation where generating the query plan takes 6s+ and
>> executing it takes very little time.
> How do you determine that it's planning time at fault here?
Not that I'm sure, but the timing I send were only for "explain" not
"explain analyze". The database is constantly updating and at the moment
i cannot reproduce it any more. But at the time I picked the numbers it
were very reproducible.. (tried 10+ times over 15 minutes).
Please take separate timing for: PREPARE testq AS select table.id
from db.table left join db.tablepro on db.id = tablepro.table_id
where table.fts @@ to_tsquery('english','q12345') ; and then:
EXPLAIN ANALYZE EXECUTE testq;
I'll try to do that if i see the problem re-occour. I'm just very
interested in what explain then does if it is not only the time for the
query plan. When I did try the "PREPARE / EXECUTE" dance as you
described .. i didnt see the prepare state take time, which seems to be
consistent with that the planning time is in the EXECUTE step according
to the documentation.
--
Jesper
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Slow query-plan generation (fast query) PG 9.2
2013-09-03 07:46 Slow query-plan generation (fast query) PG 9.2 jesper@krogh.cc
2013-09-03 07:47 ` Re: Slow query-plan generation (fast query) PG 9.2 Craig Ringer <craig@2ndquadrant.com>
2013-09-03 19:34 ` Re: Slow query-plan generation (fast query) PG 9.2 Jesper Krogh <jesper@krogh.cc>
@ 2013-09-04 13:59 ` Merlin Moncure <mmoncure@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Merlin Moncure @ 2013-09-04 13:59 UTC (permalink / raw)
To: Jesper Krogh <jesper@krogh.cc>; +Cc: Craig Ringer <craig@2ndquadrant.com>; pgsql-performance
On Tue, Sep 3, 2013 at 2:34 PM, Jesper Krogh <jesper@krogh.cc> wrote:
> On 03/09/13 09:47, Craig Ringer wrote:
>
> On 09/03/2013 03:46 PM, jesper@krogh.cc wrote:
>
> Hi.
>
> I have a strange situation where generating the query plan takes 6s+ and
> executing it takes very little time.
>
> How do you determine that it's planning time at fault here?
>
> Not that I'm sure, but the timing I send were only for "explain" not
> "explain analyze". The database is constantly updating and at the moment i
> cannot reproduce it any more. But at the time I picked the numbers it were
> very reproducible.. (tried 10+ times over 15 minutes).
Maybe your explain was blocking on locks?
merlin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2013-09-04 13:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-09-03 07:46 Slow query-plan generation (fast query) PG 9.2 jesper@krogh.cc
2013-09-03 07:47 ` Craig Ringer <craig@2ndquadrant.com>
2013-09-03 19:34 ` Jesper Krogh <jesper@krogh.cc>
2013-09-04 13:59 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox