Strange query optimization in 7.3.2 - Mailing list pgsql-general

Hello,

    I've encountered what seems to be a very strange behavior in the query
optimizer using postgresql 7.3.2.  Using a query that looks like this:

EXPLAIN ANALYZE SELECT * from (trucks tr JOIN terminals t ON (t.terminal =
tr.terminal) JOIN  manifests m ON (tr.trailer = m.trailer)) JOIN stops s ON
(m.manifest = s.manifest) WHERE ((tr.group_num = 1) AND (t.city_id = 2) AND
(s.date BETWEEN '1/1/2003' AND '1/31/2003'));

I get a somewhat slow query with a plan that is not terrbily optimal.

However when I accidentally made the query look like this:

EXPLAIN ANALYZE SELECT * from (trucks tr JOIN terminals t ON (t.terminal =
tr.terminal) JOIN  manifests m ON (tr.trailer = m.trailer)) JOIN stops s ON
(m.manifest = s.manifest) WHERE ((s.manifest = m.manifest) AND (tr.trailer =
m.trailer) AND (t.terminal = tr.terminal) AND (tr.group_num = 1) AND
(t.city_id = 2) AND (s.date BETWEEN '1/1/2003' AND '1/31/2003'));

The query plan choosen is more than three times as fast.  This is especially
strange considering it is identical to the first query apart from redundant
information for the joins.

If I change the JOIN ON to NATURAL JOIN the plan chosen is the the slow one,
unless of course the redundant information is included as well.  Using
non-explicit joins also results in the slow query plan.  The redundant
information has a drastic effect on the query plan, but makes no change to
the results.  Does anyone have any idea why this might be?

Here are the results of EXPLAIN ANALYZE for the two queries:

The Slow query:

 Hash Join  (cost=202.44..7332.06 rows=9711 width=140) (actual
time=21.20..6567.87 rows=19775 loops=1)
   Hash Cond: ("outer".manifest = "inner".manifest)
   ->  Seq Scan on stops s  (cost=0.00..6421.15 rows=117416 width=88) (actual
time=0.18..4811.78 rows=118606 loops=1)
         Filter: ((date >= '2003-01-01'::date) AND (date <=
'2003-01-31'::date))
   ->  Hash  (cost=199.64..199.64 rows=1119 width=52) (actual
time=20.54..20.54 rows=0 loops=1)
         ->  Merge Join  (cost=21.86..199.64 rows=1119 width=52) (actual
time=14.72..20.14 rows=52 loops=1)
               Merge Cond: ("outer".trailer = "inner".trailer)
               ->  Index Scan using manifests_trailer_idx on manifests m
(cost=0.00..516.82 rows=13526 width=15) (actual time=0.41..4.25 rows=174
loops=1)
               ->  Sort  (cost=21.86..22.01 rows=62 width=37) (actual
time=10.86..11.01 rows=52 loops=1)
                     Sort Key: tr.trailer
                     ->  Hash Join  (cost=1.47..20.01 rows=62 width=37)
(actual time=1.63..9.49 rows=52 loops=1)
                           Hash Cond: ("outer".terminal = "inner".terminal)
                           ->  Seq Scan on trucks tr  (cost=0.00..15.38
rows=319 width=21) (actual time=0.05..5.91 rows=319 loops=1)
                                 Filter: (group_num = 1)
                           ->  Hash  (cost=1.45..1.45 rows=7 width=16) (actual
time=0.32..0.32 rows=0 loops=1)
                                 ->  Seq Scan on terminals t  (cost=0.00..1.45
rows=7 width=16) (actual time=0.06..0.27 rows=7 loops=1)
                                       Filter: (city_id = 2)
 Total runtime: 6635.96 msec


The faster (redundant) Query:

 Nested Loop  (cost=21.86..6496.24 rows=9711 width=140) (actual
time=14.90..2045.87 rows=19775 loops=1)
   ->  Merge Join  (cost=21.86..199.64 rows=1119 width=52) (actual
time=14.67..32.01 rows=52 loops=1)
         Merge Cond: ("outer".trailer = "inner".trailer)
         ->  Index Scan using manifests_trailer_idx on manifests m
(cost=0.00..516.82 rows=13526 width=15) (actual time=0.40..4.47 rows=174
loops=1)
         ->  Sort  (cost=21.86..22.01 rows=62 width=37) (actual
time=10.80..11.02 rows=52 loops=1)
               Sort Key: tr.trailer
               ->  Hash Join  (cost=1.47..20.01 rows=62 width=37) (actual
time=1.49..9.42 rows=52 loops=1)
                     Hash Cond: ("outer".terminal = "inner".terminal)
                     ->  Seq Scan on trucks tr  (cost=0.00..15.38 rows=319
width=21) (actual time=0.04..5.88 rows=319 loops=1)
                           Filter: (group_num = 1)
                     ->  Hash  (cost=1.45..1.45 rows=7 width=16) (actual
time=0.31..0.31 rows=0 loops=1)
                           ->  Seq Scan on terminals t  (cost=0.00..1.45
rows=7 width=16) (actual time=0.06..0.26 rows=7 loops=1)
                                 Filter: (city_id = 2)
   ->  Index Scan using stops_mainfest_date_idx on stops s  (cost=0.00..5.62
rows=1 width=88) (actual time=0.13..17.13 rows=380 loops=52)
         Index Cond: (("outer".manifest = s.manifest) AND (s.manifest =
"outer".manifest) AND (s.date >= '2003-01-01'::date) AND (s.date <=
'2003-01-31'::date))
 Total runtime: 2123.36 msec

As you can see the planner estimates are all the same for the operations
common to both plans, but a different choice is made for the final (big)
join.  Thanks in advance for any enlightenment on this strange issue.  I'd
rather not be using such a kludgy query to optimize things unless I have no
other option.

Alec Mitchell