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