postgres FROM clause problem - Mailing list pgsql-general

I need help!


I suppose that  for query like :

SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;

the planner is free to join the given tables in any order to find the most efficent query plan.



I have two query that they are different only for order of the tables in FROM lclause , but give back different query
plan:  

1)   ###################

explain analyze SELECT
    teatri.code,
    spettacoli.code,
    sum(seat.price)
    sum(seat.prev)

  FROM
    seat,
    spettacoli,
    tran ,
    teatri

  WHERE
    tran.time >= timestamp '2004-03-01 00:00:00' AND
    tran.time <= timestamp '2004-03-08 23:59:59' AND
    tran.perf_code           = spettacoli.code AND
    tran.thea_code           = spettacoli.teatro AND
    tran.id                  = seat.bt_id AND
    tran.system              = seat.system AND
    spettacoli.system        = tran.system AND
    spettacoli.teatro        = teatri.code
  GROUP BY
    1,2

with EXPLAIN :



HashAggregate  (cost=8540.03..8540.20 rows=5 width=30) (actual  time=3210.659..3212.403 rows=1317 loops=1)
   ->  Nested Loop  (cost=7853.66..8539.98 rows=5 width=30) (actual time=1316.663..2822.181 rows=67218 loops=1)
         ->  Nested Loop  (cost=7853.66..8516.75 rows=5 width=43) (actual time=1316.625..1901.870 rows=68167 loops=1)
               ->  Merge Join  (cost=7853.66..8462.71 rows=9 width=47) (actual time=1316.555..1519.227 rows=24919
loops=1)
                     Merge Cond: (("outer"."?column4?" = "inner"."?column6?") AND ("outer".system = "inner".system) AND
("outer".teatro= "inner".thea_code)) 
                     ->  Sort  (cost=6158.07..6298.27 rows=56079 width=26) (actual time=650.575..658.869 rows=48297
loops=1)
                           Sort Key: (spettacoli.code)::text, spettacoli.system, spettacoli.teatro
                           ->  Seq Scan on spettacoli  (cost=0.00..1734.79 rows=56079 width=26) (actual
time=0.020..94.725rows=56079 loops=1) 
                     ->  Sort  (cost=1695.59..1727.03 rows=12576 width=32) (actual time=619.904..625.457 rows=24923
loops=1)
                           Sort Key: (tran.perf_code)::text, tran.system, tran.thea_code
                           ->  Index Scan using time_idx on tran  (cost=0.00..839.27 rows=12576 width=32) (actual
time=0.121..104.945rows=24923 loops=1) 
                                 Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp without time
zone)::timestampwith time zone) AND ("time" <= ('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with
timezone)) 
                                 Filter: ((kind <> 9) AND (kind <> 10) AND (kind <> 3) AND (kind <> 7) AND (status =
0))
               ->  Index Scan using id_system_idx on seat  (cost=0.00..5.99 rows=1 width=10) (actual time=0.007..0.010
rows=3loops=24919) 
                     Index Cond: (("outer".id = seat.bt_id) AND ("outer".system = seat.system))
         ->  Index Scan using teatri_pkey on teatri  (cost=0.00..4.63 rows=1 width=13) (actual time=0.010..0.011 rows=1
loops=68167)
               Index Cond: (teatri.code = "outer".thea_code)
 Total runtime: 3224.260 ms


2)

explain analyze SELECT
    teatri.code,
    spettacoli.code,
    sum(seat.price),
    sum(seat.prev)


  FROM
    seat,
    teatri,
    tran ,
    spettacoli

  WHERE
    tran.time >= timestamp '2004-03-01 00:00:00' AND
    tran.time <= timestamp '2004-03-08 23:59:59' AND
    tran.perf_code           = spettacoli.code AND
    tran.thea_code           = spettacoli.teatro AND
    tran.id                  = seat.bt_id AND
    tran.system              = seat.system AND
    spettacoli.system        = tran.system AND
    spettacoli.teatro        = teatri.code
  GROUP BY
    1,2


with EXPLAIN :


 HashAggregate  (cost=8355.46..8355.49 rows=1 width=30) (actual time=48126.117..48127.715 rows=1317 loops=1)
   ->  Nested Loop  (cost=1909.00..8355.45 rows=1 width=30) (actual time=504.927..47738.287 rows=67218 loops=1)
         ->  Merge Join  (cost=1909.00..8301.41 rows=9 width=34) (actual time=504.873..47385.221 rows=24788 loops=1)
               Merge Cond: (("outer".system = "inner".system) AND ("outer".teatro = "inner".code))
               Join Filter: (("inner".perf_code)::text = ("outer".code)::text)
               ->  Index Scan using spe_sys_tea_perf on spettacoli  (cost=0.00..5856.59 rows=56079 width=26) (actual
time=0.019..67.663rows=55352 loops=1) 
               ->  Sort  (cost=1909.00..1940.44 rows=12576 width=45) (actual time=501.736..2592.348 rows=12816353
loops=1)
                     Sort Key: tran.system, teatri.code
                     ->  Hash Join  (cost=24.78..1052.68 rows=12576 width=45) (actual time=1.581..190.530 rows=24792
loops=1)
                           Hash Cond: ("outer".thea_code = "inner".code)
                           ->  Index Scan using time_idx on tran  (cost=0.00..839.27 rows=12576 width=32) (actual
time=0.074..67.643rows=24923 loops=1) 
                                 Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp without time
zone)::timestampwith time zone) AND ("time" <= ('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with
timezone)) 
                                 Filter: ((kind <> 9) AND (kind <> 10) AND (kind <> 3) AND (kind <> 7) AND (status =
0))
                           ->  Hash  (cost=22.62..22.62 rows=862 width=13) (actual time=1.348..1.348 rows=0 loops=1)
                                 ->  Seq Scan on teatri  (cost=0.00..22.62 rows=862 width=13) (actual time=0.011..0.792
rows=862loops=1) 
         ->  Index Scan using id_system_idx on seat  (cost=0.00..5.99 rows=1 width=10) (actual time=0.006..0.009 rows=3
loops=24788)
               Index Cond: (("outer".id = seat.bt_id) AND ("outer".system = seat.system))

 Total runtime: 48131.798 ms


Why this difference?
i must not have the same plan in both the cases?
I use postgres 7.4
thank you!