From: | Paolo Tavalazzi <ptavalazzi(at)charta(dot)it> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: postgres FROM clause problem |
Date: | 2004-03-11 17:25:48 |
Message-ID: | 200403111825.48991.ptavalazzi@charta.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Csaba Nagy | 2004-03-11 17:48:11 | Re: "make check" fails for 7.4.2 checked out from CVS |
Previous Message | javier garcia - CEBAS | 2004-03-11 17:10:42 | Re: creating sequential timestamp |