Is Hash Agg being used? 7.4 seems to handle this query worse than 7.3 - Mailing list pgsql-general

Hm, CVS doesn't seem to be using a hash aggregate. At least, if it is it isn't
obvious from the plan.

The query actually runs slightly slower in CVS than with 7.3, though it's hard
to compare because it seems to have done everything differently. Every hash
join has become a merge join and every merge join has become a hash join :/



SELECT hier.level_0_id as parent_id,
       (select localized_text from localized_text where text_id = hier.short_name_text_id and lang_code = 'en') as
name,
       *
  FROM hier LEFT OUTER JOIN (
        SELECT distinct level_0_id, level_1_id
          FROM cache_foo JOIN foo_hier USING (foo_id)
         WHERE key_value = 839
           AND dist < 60
     ) AS cache ON (hier.hier_id = cache.level_1_id)
 WHERE level = 1
 ORDER BY 1,2



The cvs plan:

                                                                                   QUERY PLAN
                                                        

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=3691.30..3691.49 rows=76 width=1584) (actual time=917.19..917.23 rows=31 loops=1)
   Sort Key: hier.level_0_id, (subplan)
   ->  Hash Left Join  (cost=2837.80..3688.92 rows=76 width=1584) (actual time=914.46..916.89 rows=31 loops=1)
         Hash Cond: ("outer".hier_id = "inner".level_1_id)
         ->  Seq Scan on hier  (cost=0.00..63.86 rows=14 width=1576) (actual time=7.16..7.37 rows=31 loops=1)
               Filter: ("level" = 1)
         ->  Hash  (cost=2799.99..2799.99 rows=15122 width=16) (actual time=905.99..905.99 rows=0 loops=1)
               ->  Subquery Scan "cache"  (cost=2686.58..2799.99 rows=15122 width=16) (actual time=853.43..905.84
rows=31loops=1) 
                     ->  Unique  (cost=2686.58..2799.99 rows=15122 width=16) (actual time=853.41..905.68 rows=31
loops=1)
                           ->  Sort  (cost=2686.58..2724.38 rows=15122 width=16) (actual time=853.40..873.00 rows=16440
loops=1)
                                 Sort Key: foo_hier.level_0_id, foo_hier.level_1_id
                                 ->  Merge Join  (cost=123.56..1636.78 rows=15122 width=16) (actual time=248.54..723.14
rows=16440loops=1) 
                                       Merge Cond: ("outer".foo_id = "inner".foo_id)
                                       ->  Index Scan using foo_hier_foo on foo_hier  (cost=0.00..1173.54 rows=45140
width=12)(actual time=0.04..234.30 rows=45140 loops=1) 
                                       ->  Sort  (cost=123.56..123.73 rows=67 width=4) (actual time=248.43..267.31
rows=16441loops=1) 
                                             Sort Key: cache_foo.foo_id
                                             ->  Index Scan using idx_cache_foo on cache_foo  (cost=0.00..121.53
rows=67width=4) (actual time=0.06..116.21 rows=16486 loops=1) 
                                                   Index Cond: ((key_value = 839) AND (dist < 60::double precision))
         SubPlan
           ->  Index Scan using localized_text_pkey on localized_text  (cost=0.00..4.01 rows=1 width=516) (actual
time=0.03..0.05rows=1 loops=31) 
                 Index Cond: ((text_id = $0) AND (lang_code = 'en'::bpchar))
 Total runtime: 928.46 msec



The 7.3 plan:

                                                                                  QUERY PLAN
                                                      

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4209.04..4209.12 rows=31 width=85) (actual time=849.01..849.05 rows=31 loops=1)
   Sort Key: hier.level_0_id, (subplan)
   ->  Merge Join  (cost=4199.51..4208.27 rows=31 width=85) (actual time=846.77..848.69 rows=31 loops=1)
         Merge Cond: ("outer".hier_id = "inner".level_1_id)
         ->  Sort  (cost=64.63..64.71 rows=31 width=77) (actual time=8.38..8.42 rows=31 loops=1)
               Sort Key: hier.hier_id
               ->  Seq Scan on hier  (cost=0.00..63.86 rows=31 width=77) (actual time=7.84..8.20 rows=31 loops=1)
                     Filter: ("level" = 1)
         ->  Sort  (cost=4134.88..4139.07 rows=1674 width=16) (actual time=837.74..837.78 rows=31 loops=1)
               Sort Key: "cache".level_1_id
               ->  Subquery Scan "cache"  (cost=3919.66..4045.23 rows=1674 width=16) (actual time=786.64..837.54
rows=31loops=1) 
                     ->  Unique  (cost=3919.66..4045.23 rows=1674 width=16) (actual time=786.63..837.38 rows=31
loops=1)
                           ->  Sort  (cost=3919.66..3961.52 rows=16743 width=16) (actual time=786.61..804.71 rows=16440
loops=1)
                                 Sort Key: foo_hier.level_0_id, foo_hier.level_1_id
                                 ->  Hash Join  (cost=1599.78..2745.06 rows=16743 width=16) (actual time=349.16..628.43
rows=16440loops=1) 
                                       Hash Cond: ("outer".foo_id = "inner".foo_id)
                                       ->  Index Scan using idx_cache_foo on cache_foo  (cost=0.00..810.43 rows=16743
width=4)(actual time=0.07..144.44 rows=16486 loops=1) 
                                             Index Cond: ((key_value = 839) AND (dist < 60::double precision))
                                       ->  Hash  (cost=746.40..746.40 rows=45140 width=12) (actual time=347.32..347.32
rows=0loops=1) 
                                             ->  Seq Scan on foo_hier  (cost=0.00..746.40 rows=45140 width=12) (actual
time=0.05..222.63rows=45140 loops=1) 
         SubPlan
           ->  Index Scan using localized_text_pkey on localized_text  (cost=0.00..4.03 rows=1 width=17) (actual
time=0.03..0.03rows=1 loops=31) 
                 Index Cond: ((text_id = $0) AND (lang_code = 'en'::bpchar))
 Total runtime: 854.57 msec



--
greg