Re: postgres FROM clause problem - Mailing list pgsql-general

Alle 18:03, giovedì 11 marzo 2004, hai scritto:
> Paolo Tavalazzi <ptavalazzi@charta.it> writes:
> > But the query plans are still various!!
>
> I think you made a copy-and-paste mistake, because the explain results
> you posted are exactly the same ...
>
>             regards, tom lane


 Excuse I have mistaken!!


#### QUERY 1



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



  FROM
    seat,
    spettacoli,
    tran ,
    teatri


  WHERE
    tran.time >= timestamp '2004-02-20 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


EXPLAIN :

 HashAggregate  (cost=8042.89..8042.92 rows=7 width=32) (actual time=2069.895..2071.505 rows=1317 loops=1)
   ->  Hash Join  (cost=8015.82..8042.82 rows=7 width=32) (actual time=1538.771..1779.257 rows=67218 loops=1)
         Hash Cond: ("outer".code = "inner".teatro)
         ->  Seq Scan on teatri  (cost=0.00..22.62 rows=862 width=13) (actual time=0.008..0.572 rows=862 loops=1)
         ->  Hash  (cost=8015.80..8015.80 rows=7 width=45) (actual time=1538.652..1538.652 rows=0 loops=1)
               ->  Nested Loop  (cost=1538.40..8015.80 rows=7 width=45) (actual time=652.105..1486.577 rows=68167
loops=1)
                     ->  Merge Join  (cost=1538.40..7920.98 rows=16 width=45) (actual time=652.045..1095.559 rows=24919
loops=1)
                           Merge Cond: (("outer".system = "inner".system) AND ("outer".teatro = "inner".thea_code) AND
(("outer".code)::text= "inner"."?column5?")) 
                           ->  Index Scan using spe_sys_tea_perf on spettacoli  (cost=0.00..5836.07 rows=56079
width=26)(actual time=0.049..63.556 rows=55565 loops=1) 
                           ->  Sort  (cost=1538.40..1569.84 rows=12576 width=30) (actual time=651.509..656.391
rows=24923loops=1) 
                                 Sort Key: tran.system, tran.thea_code, (tran.perf_code)::text
                                 ->  Index Scan using time_idx on tran  (cost=0.00..682.08 rows=12576 width=30) (actual
time=0.083..69.887rows=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)) 
                     ->  Index Scan using id_system_idx on seat  (cost=0.00..5.91 rows=1 width=14) (actual
time=0.006..0.010rows=3 loops=24919) 
                           Index Cond: (("outer".id = seat.bt_id) AND ("outer".system = seat.system))
 Total runtime: 2076.726 ms


#### QUERY 2 :

explain anlyze 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


EXPLAIN output :

 HashAggregate  (cost=8057.62..8057.63 rows=1 width=32) (actual time=2728.066..2729.738 rows=1317 loops=1)
   ->  Nested Loop  (cost=1538.40..8057.61 rows=1 width=32) (actual time=665.122..2438.275 rows=67218 loops=1)
         ->  Nested Loop  (cost=1538.40..8015.80 rows=9 width=45) (actual time=665.078..1509.890 rows=68167 loops=1)
               ->  Merge Join  (cost=1538.40..7920.98 rows=16 width=45) (actual time=665.018..1101.716 rows=24919
loops=1)
                     Merge Cond: (("outer".system = "inner".system) AND ("outer".teatro = "inner".thea_code) AND
(("outer".code)::text= "inner"."?column5?")) 
                     ->  Index Scan using spe_sys_tea_perf on spettacoli  (cost=0.00..5836.07 rows=56079 width=26)
(actualtime=0.046..63.772 rows=55565 loops=1) 
                     ->  Sort  (cost=1538.40..1569.84 rows=12576 width=30) (actual time=664.481..669.947 rows=24923
loops=1)
                           Sort Key: tran.system, tran.thea_code, (tran.perf_code)::text
                           ->  Index Scan using time_idx on tran  (cost=0.00..682.08 rows=12576 width=30) (actual
time=0.080..70.663rows=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)) 
               ->  Index Scan using id_system_idx on seat  (cost=0.00..5.91 rows=1 width=14) (actual time=0.007..0.009
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: 2733.910 ms