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

Alle 19:40, mercoledì 10 marzo 2004, hai scritto:
> Paolo Tavalazzi <ptavalazzi@charta.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)
(actualtime=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.057rows=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.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: 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)
(actualtime=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.057rows=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.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: 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