Re: Inconsistant query plan

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