Re: postgres FROM clause problem

Alle 18:03, giovedì 11 marzo 2004, hai scritto:
> Paolo Tavalazzi <ptavalazzi(at)charta(dot)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=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.083..69.887 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp without time zone)::timestamp with time zone) AND ("time" <= ('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..5.91 rows=1 width=14) (actual time=0.006..0.010 rows=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) (actual time=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.663 rows=24923 loops=1)
Index Cond: (("time" >= ('2004-03-01 00:00:00'::timestamp without time zone)::timestamp with time zone) AND ("time" <= ('2004-03-08 23:59:59'::timestamp without time zone)::timestamp with time zone))
-> Index Scan using id_system_idx on seat (cost=0.00..5.91 rows=1 width=14) (actual time=0.007..0.009 rows=3 loops=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