pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedInconsistant query plan
5+ messages / 4 participants
[nested] [flat]
* Inconsistant query plan
@ 2006-01-24 23:15 Daniel Gish <dan@centrifugesolutions.com>
2006-01-24 23:30 ` Re: Inconsistant query plan Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-24 23:58 ` Re: Inconsistant query plan Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 5+ messages in thread
From: Daniel Gish @ 2006-01-24 23:15 UTC (permalink / raw)
To: pgsql-performance
Hi,
We are running Postgresql 8.1, and getting dramatically inconsistant results
after running VACUUM ANALYZE. Sometimes after analyzing the database, the
query planner chooses a very efficient plan (15 rows, 4.744 ms), and
sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated
query:
SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid =
t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ...
In the efficient plan, t2 is joined to t3 & t4 before being joined to t1.
The inefficient plan joins t1 to t2 before joining to the other tables.
We've experimented with different settings, such as shared_buffers &
max_fsm_pages, to no avail. Anybody have a suggestion for getting the
efficient plan to execute consistantly? If you'd like to see the actual
query & query plans let me know.
Best Regards,
Dan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistant query plan
2006-01-24 23:15 Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
@ 2006-01-24 23:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2006-01-24 23:30 UTC (permalink / raw)
To: Daniel Gish <dan@centrifugesolutions.com>; +Cc: pgsql-performance
On Tue, 2006-01-24 at 17:15, Daniel Gish wrote:
> Hi,
> We are running Postgresql 8.1, and getting dramatically inconsistant results
> after running VACUUM ANALYZE. Sometimes after analyzing the database, the
> query planner chooses a very efficient plan (15 rows, 4.744 ms), and
> sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated
> query:
>
> SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid =
> t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ...
>
> In the efficient plan, t2 is joined to t3 & t4 before being joined to t1.
> The inefficient plan joins t1 to t2 before joining to the other tables.
>
> We've experimented with different settings, such as shared_buffers &
> max_fsm_pages, to no avail. Anybody have a suggestion for getting the
> efficient plan to execute consistantly? If you'd like to see the actual
> query & query plans let me know.
Have you adjusted the stats target for that column? See \h alter table
in psql for the syntax for that. Then run analyze again.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistant query plan
2006-01-24 23:15 Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
@ 2006-01-24 23:58 ` Michael Fuhr <mike@fuhr.org>
2006-01-25 01:04 ` Re: Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
1 sibling, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2006-01-24 23:58 UTC (permalink / raw)
To: Daniel Gish <dan@centrifugesolutions.com>; +Cc: pgsql-performance
On Tue, Jan 24, 2006 at 04:15:57PM -0700, Daniel Gish wrote:
> We are running Postgresql 8.1, and getting dramatically inconsistant results
> after running VACUUM ANALYZE. Sometimes after analyzing the database, the
> query planner chooses a very efficient plan (15 rows, 4.744 ms), and
> sometimes a terrible one (24 rows, 3536.995 ms). Here's the abbreviated
> query:
>
> SELECT * FROM t1 INNER JOIN (t2 INNER JOIN (t3 INNER JOIN t4 ON t3.gid =
> t4.gid) ON t3.gid = t2.gid) ON t2.eid = t1.eid WHERE ...
How abbreviated is that example? Are you actually joining more
tables than that? In another recent thread varying plans were
attributed to exceeding geqo_threshold:
http://archives.postgresql.org/pgsql-performance/2006-01/msg00132.php
Does your situation look similar?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistant query plan
2006-01-24 23:15 Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
2006-01-24 23:58 ` Re: Inconsistant query plan Michael Fuhr <mike@fuhr.org>
@ 2006-01-25 01:04 ` Daniel Gish <dan@centrifugesolutions.com>
2006-01-25 09:05 ` Re: Inconsistant query plan Alessandro Baretta <a.baretta@barettadeit.com>
0 siblings, 1 reply; 5+ messages in thread
From: Daniel Gish @ 2006-01-25 01:04 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance
Hi,
Thanks for your response. The actual query is below; the joins are only 4
deep. Adjusting the stats target did help, but not dramatically.
EFFICIENT PLAN:
# explain analyze SELECT ev.eid FROM events ev INNER JOIN (events_join ej
INNER JOIN (groups_join gj INNER JOIN groups g ON gj.gid = g.gid) ON ej.gid
= gj.gid) ON ev.eid = ej.eid WHERE ev.status > 0 AND ej.type_id = 1 AND
g.deleted = 'f' AND g.deactivated != 't' AND ev.type_id >= 0 AND gj.uid=3
AND ev.timestart BETWEEN '01/23/2006'::timestamp AND '02/23/2006'::timestamp
+ '1 day - 1 minute';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------
Nested Loop (cost=0.00..8370.41 rows=25 width=4) (actual time=4.510..4.510
rows=0 loops=1)
-> Nested Loop (cost=0.00..6124.63 rows=673 width=4) (actual
time=0.132..3.116 rows=92 loops=1)
-> Nested Loop (cost=0.00..70.95 rows=8 width=8) (actual
time=0.080..2.226 rows=19 loops=1)
-> Index Scan using groups_join_uid_idx on groups_join gj
(cost=0.00..16.27 rows=11 width=4) (actual time=0.019..0.471 rows=196
loops=1)
Index Cond: (uid = 3)
-> Index Scan using groups_pkey on groups g
(cost=0.00..4.96 rows=1 width=4) (actual time=0.005..0.006 rows=0 loops=196)
Index Cond: ("outer".gid = g.gid)
Filter: ((NOT deleted) AND (deactivated <> true))
-> Index Scan using events_join_gid_idx on events_join ej
(cost=0.00..752.45 rows=341 width=8) (actual time=0.010..0.027 rows=5
loops=19)
Index Cond: (ej.gid = "outer".gid)
Filter: (type_id = 1)
-> Index Scan using events_pkey on events ev (cost=0.00..3.32 rows=1
width=4) (actual time=0.012..0.012 rows=0 loops=92)
Index Cond: (ev.eid = "outer".eid)
Filter: ((status > 0) AND (type_id >= 0) AND (timestart >=
'2006-01-23 00:00:00'::timestamp without time zone) AND (timestart <=
'2006-02-23 23:59:00'::timestamp without time zone))
Total runtime: 4.744 ms
(15 rows)
INEFFICIENT PLAN:
# explain analyze SELECT ev.eid FROM events ev INNER JOIN (events_join ej
INNER JOIN (groups_join gj INNER JOIN groups g ON gj.gid = g.gid) ON ej.gid
= g.gid) ON ev.eid = ej.eid WHERE ev.status > 0 AND ej.type_id = 1 AND
g.deleted = 'f' AND g.deactivated != 't' AND ev.type_id >= 0 AND gj.uid=3
AND ev.timestart BETWEEN '01/23/2006'::timestamp AND '02/23/2006'::timestamp
+ '1 day - 1 minute';
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------------------------------------
Nested Loop (cost=978.19..37161.81 rows=133 width=4) (actual
time=2511.676..2511.676 rows=0 loops=1)
-> Merge Join (cost=978.19..22854.00 rows=4244 width=4) (actual
time=1718.420..2510.128 rows=92 loops=1)
Merge Cond: ("outer".gid = "inner".gid)
-> Index Scan using events_join_gid_idx on events_join ej
(cost=0.00..23452.59 rows=740598 width=8) (actual time=0.014..1532.447
rows=626651 loops=1)
Filter: (type_id = 1)
-> Sort (cost=978.19..978.47 rows=113 width=8) (actual
time=2.371..2.540 rows=101 loops=1)
Sort Key: g.gid
-> Nested Loop (cost=0.00..974.33 rows=113 width=8) (actual
time=0.078..2.305 rows=19 loops=1)
-> Index Scan using groups_join_uid_idx on groups_join
gj (cost=0.00..182.65 rows=159 width=4) (actual time=0.017..0.485 rows=196
loops=1)
Index Cond: (uid = 3)
-> Index Scan using groups_pkey on groups g
(cost=0.00..4.97 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=196)
Index Cond: ("outer".gid = g.gid)
Filter: ((NOT deleted) AND (deactivated <> true))
-> Index Scan using events_pkey on events ev (cost=0.00..3.36 rows=1
width=4) (actual time=0.013..0.013 rows=0 loops=92)
Index Cond: (ev.eid = "outer".eid)
Filter: ((status > 0) AND (type_id >= 0) AND (timestart >=
'2006-01-23 00:00:00'::timestamp without time zone) AND (timestart <=
'2006-02-23 23:59:00'::timestamp without time zone))
Total runtime: 2511.920 ms
(17 rows)
Regards,
Dan
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Inconsistant query plan
2006-01-24 23:15 Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
2006-01-24 23:58 ` Re: Inconsistant query plan Michael Fuhr <mike@fuhr.org>
2006-01-25 01:04 ` Re: Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
@ 2006-01-25 09:05 ` Alessandro Baretta <a.baretta@barettadeit.com>
0 siblings, 0 replies; 5+ messages in thread
From: Alessandro Baretta @ 2006-01-25 09:05 UTC (permalink / raw)
To: Daniel Gish <dan@centrifugesolutions.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-performance
Daniel Gish wrote:
> Hi,
> Thanks for your response. The actual query is below; the joins are only 4
> deep. Adjusting the stats target did help, but not dramatically.
>
> QUERY PLAN
> ----------------------------------------------------------------------------
> ----------------------------------------------------------------------------
> ---------------------------------------
> Nested Loop (cost=978.19..37161.81 rows=133 width=4) (actual
> time=2511.676..2511.676 rows=0 loops=1)
> -> Merge Join (cost=978.19..22854.00 rows=4244 width=4) (actual
> time=1718.420..2510.128 rows=92 loops=1)
> ...
> -> Nested Loop (cost=0.00..974.33 rows=113 width=8) (actual
time=0.078..2.305 rows=19 loops=1)
I have a similar problem recently. An importat diagnostic tool for these issues
is the pg_stats view. Let me suggest that you post the relevant lines from
pg_stats, so that with some help you will be able to discover what data advises
the query planner to overestimate the cardinality of some joins and
underestimate others.
Alex
--
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL
tel. +39 02 370 111 55
fax. +39 02 370 111 54
Our technology:
The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/;
The FreerP Project
<http://www.freerp.org/;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-01-25 09:05 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-24 23:15 Inconsistant query plan Daniel Gish <dan@centrifugesolutions.com>
2006-01-24 23:30 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-01-24 23:58 ` Michael Fuhr <mike@fuhr.org>
2006-01-25 01:04 ` Daniel Gish <dan@centrifugesolutions.com>
2006-01-25 09:05 ` Alessandro Baretta <a.baretta@barettadeit.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