Re: postgres FROM clause problem

Alle 19:40, mercoledì 10 marzo 2004, hai scritto:
> Paolo Tavalazzi <ptavalazzi(at)charta(dot)it> writes:
> > I have applied the procedure for fixing pg_statistic as you had said,
> > but the result is the same!
>
> Hm. It could be a planner bug. Can you reproduce the misbehavior if
> you dump and load the tables into a fresh database? If so, could you
> send me the dump so I can look at the problem with a debugger?
>
> (Also, you might try updating to 7.4.2 first and see if that changes
> anything. We did fix quite a number of bugs already in 7.4.2...)
>
> > Another question, in 7.4 GROUP BY clause not guarantee the ordering of
> > result. Can I desable the new group by algorithm to maintain the
> > coherence whit the programs that I have in production with pg_7.3
>
> As Bruno said, your programs are broken because they are assuming
> something not guaranteed by the SQL spec. But until you get around to
> adding the ORDER BY clauses they should have, see enable_hashagg.
>
> regards, tom lane

I update to 7.4.2 (dump - initdb -restore) and I have had this result :

#### 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 output :

HashAggregate (cost=8057.62..8057.63 rows=1 width=32) (actual time=2711.656..2713.337 rows=1317 loops=1)
-> Nested Loop (cost=1538.40..8057.61 rows=1 width=32) (actual time=648.011..2425.314 rows=67218 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=9 width=45) (actual time=647.968..1494.797 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual time=647.909..1087.032 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..64.118 rows=55565 loops=1)
-> Sort (cost=1538.40..1569.84 rows=12576 width=30) (actual time=647.374..653.048 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.082..73.057 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.010 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: 2717.573 ms

#### QUERY 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

EXPLAIN output :

HashAggregate (cost=8057.62..8057.63 rows=1 width=32) (actual time=2711.656..2713.337 rows=1317 loops=1)
-> Nested Loop (cost=1538.40..8057.61 rows=1 width=32) (actual time=648.011..2425.314 rows=67218 loops=1)
-> Nested Loop (cost=1538.40..8015.80 rows=9 width=45) (actual time=647.968..1494.797 rows=68167 loops=1)
-> Merge Join (cost=1538.40..7920.98 rows=16 width=45) (actual time=647.909..1087.032 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..64.118 rows=55565 loops=1)
-> Sort (cost=1538.40..1569.84 rows=12576 width=30) (actual time=647.374..653.048 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.082..73.057 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.010 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: 2717.573 ms

As you see the query they have similar result times, the second one is a lot of fast and optimized then before!
But the query plans are still various!!
For me the result is satisfactory , but i don't understand why thw two query give back different plan???
thank you