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

Tom Lane <tgl@sss.pgh.pa.us> writes:

> You could probably persuade it that hashed aggregation will be okay by
> increasing sort_mem sufficiently.  But it would also be interesting to
> see if the number-of-groups estimate can be improved ... 15122 is rather
> badly off from the true value of 31 ...

I don't see how you're ever going to reliably come up with a good estimate for
this. Consider that it's not just the distribution of the column that matters,
but the distribution given the where clauses in effect. This is dependent on
what degree the expressions in the where clauses are independent of the
expressions we're grouping on, which is hard to predict in foovance.

If the prediction is wrong is it just a performance penalty? The hash can
still proceed if it has to go to disk? In which case is there a way for me to
force it to use hashes if I know better than the optimizer?

I've run vacuum full and analyze on both databases again. The data should be
identical as I've just copied the database and I haven't updated anything. Two
example queries tested with both. It isn't using hash aggregates for either.
The plans are still quite different.


This is the previous query except I've added hier.level_0_id to the join
clause in the hopes it would skip the redundant sort. It didn't work, though
the second sort is fast due to there being relatively few records coming out
of the group.

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 min(dist) AS mindist, count(distinct foo_id) AS num_foos, level_1_id, level_0_id
          FROM cache_foos JOIN foo_hier USING (foo_id)
         WHERE region_id = 839
           AND dist < 60
         GROUP BY level_0_id, level_1_id
     ) AS cache ON (hier.hier_id = cache.level_1_id and hier.level_0_id = cache.level_0_id)
 WHERE level = 1
 ORDER BY hier.level_0_id, hier.level_1_id

CVS:
                                                                                  QUERY PLAN
                                                      

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4091.65..4091.73 rows=31 width=101) (actual time=907.95..907.99 rows=31 loops=1)
   Sort Key: hier.level_0_id, hier.level_1_id
   ->  Merge Left Join  (cost=3964.22..4090.89 rows=31 width=101) (actual time=905.53..907.61 rows=31 loops=1)
         Merge Cond: (("outer".level_0_id = "inner".level_0_id) AND ("outer".hier_id = "inner".level_1_id))
         ->  Sort  (cost=64.63..64.71 rows=31 width=77) (actual time=7.61..7.64 rows=31 loops=1)
               Sort Key: hier.level_0_id, hier.hier_id
               ->  Seq Scan on hier  (cost=0.00..63.86 rows=31 width=77) (actual time=6.47..7.36 rows=31 loops=1)
                     Filter: ("level" = 1)
         ->  Sort  (cost=3899.59..3899.90 rows=124 width=24) (actual time=897.76..897.80 rows=31 loops=1)
               Sort Key: "cache".level_0_id, "cache".level_1_id
               ->  Subquery Scan "cache"  (cost=3697.05..3895.28 rows=124 width=24) (actual time=771.57..897.28 rows=31
loops=1)
                     ->  GroupAggregate  (cost=3697.05..3895.28 rows=124 width=24) (actual time=771.55..897.03 rows=31
loops=1)
                           ->  Sort  (cost=3697.05..3736.57 rows=15809 width=24) (actual time=764.08..782.64 rows=16440
loops=1)
                                 Sort Key: foo_hier.level_0_id, foo_hier.level_1_id
                                 ->  Hash Join  (cost=853.20..2594.49 rows=15809 width=24) (actual time=307.51..603.83
rows=16440loops=1) 
                                       Hash Cond: ("outer".foo_id = "inner".foo_id)
                                       ->  Index Scan using idx_cache_foos on cache_foos  (cost=0.00..752.94 rows=15808
width=12)(actual time=0.08..111.71 rows=16486 loops=1) 
                                             Index Cond: ((region_id = 839) AND (dist < 60::double precision))
                                       ->  Hash  (cost=740.16..740.16 rows=45216 width=12) (actual time=306.11..306.11
rows=0loops=1) 
                                             ->  Seq Scan on foo_hier  (cost=0.00..740.16 rows=45216 width=12) (actual
time=0.03..143.56rows=45140 loops=1) 
         SubPlan
           ->  Index Scan using localized_text_pkey on localized_text  (cost=0.00..4.05 rows=2 width=17) (actual
time=0.03..0.03rows=1 loops=31) 
                 Index Cond: ((text_id = $0) AND (lang_code = 'en'::bpchar))
 Total runtime: 915.22 msec


7.3:
                                                                                      QUERY PLAN
                                                             

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=4056.43..4056.51 rows=31 width=101) (actual time=1036.93..1036.97 rows=31 loops=1)
   Sort Key: hier.level_0_id, hier.level_1_id
   ->  Merge Join  (cost=4047.39..4055.66 rows=31 width=101) (actual time=1034.53..1036.59 rows=31 loops=1)
         Merge Cond: (("outer".level_0_id = "inner".level_0_id) AND ("outer".hier_id = "inner".level_1_id))
         ->  Sort  (cost=64.63..64.71 rows=31 width=77) (actual time=10.15..10.18 rows=31 loops=1)
               Sort Key: hier.level_0_id, hier.hier_id
               ->  Seq Scan on hier  (cost=0.00..63.86 rows=31 width=77) (actual time=9.59..9.94 rows=31 loops=1)
                     Filter: ("level" = 1)
         ->  Sort  (cost=3982.76..3986.82 rows=1624 width=24) (actual time=1024.23..1024.26 rows=31 loops=1)
               Sort Key: "cache".level_0_id, "cache".level_1_id
               ->  Subquery Scan "cache"  (cost=3693.23..3896.18 rows=1624 width=24) (actual time=835.96..1023.98
rows=31loops=1) 
                     ->  Aggregate  (cost=3693.23..3896.18 rows=1624 width=24) (actual time=835.95..1023.76 rows=31
loops=1)
                           ->  Group  (cost=3693.23..3815.00 rows=16236 width=24) (actual time=828.96..941.70
rows=16440loops=1) 
                                 ->  Sort  (cost=3693.23..3733.82 rows=16236 width=24) (actual time=828.93..848.83
rows=16440loops=1) 
                                       Sort Key: foo_hier.level_0_id, foo_hier.level_1_id
                                       ->  Hash Join  (cost=1432.53..2557.77 rows=16236 width=24) (actual
time=350.81..670.83rows=16440 loops=1) 
                                             Hash Cond: ("outer".foo_id = "inner".foo_id)
                                             ->  Index Scan using idx_cache_foos on cache_foos  (cost=0.00..800.51
rows=16236width=12) (actual time=0.08..159.95 rows=16486 loops=1) 
                                                   Index Cond: ((region_id = 839) AND (dist < 60::double precision))
                                             ->  Hash  (cost=746.35..746.35 rows=45135 width=12) (actual
time=349.15..349.15rows=0 loops=1) 
                                                   ->  Seq Scan on foo_hier  (cost=0.00..746.35 rows=45135 width=12)
(actualtime=0.06..219.15 rows=45140 loops=1) 
         SubPlan
           ->  Index Scan using localized_text_pkey on localized_text  (cost=0.00..4.10 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: 1042.86 msec





This is another query, one that involves a distinct on and a group. I guess
the plan for this one could be different because the JOIN no longer constrains
the join order. Though the order seems the same to me, it's the statistics
that seem to be different.


SELECT *
  FROM (
        SELECT DISTINCT ON (bar_id)
               bar_id, bar_location_id, num_foos, mindist
          FROM (
                SELECT bar_id, bar_location_id, count(distinct foo_id) as num_foos, min(dist) as mindist
                  FROM cache_foos
                 WHERE region_id = 839
                   AND dist < 60
                   AND foo_id is not null
                 GROUP BY bar_id, bar_location_id
                ) as x
         ORDER BY bar_id, mindist asc
      ) AS cache
   JOIN bar using (bar_id)
   JOIN bar_location using (bar_location_id)


CVS:

                                                                                           QUERY PLAN
                                                                        

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=3438.87..3815.85 rows=200 width=654) (actual time=470.72..533.88 rows=112 loops=1)
   Merge Cond: ("outer".bar_location_id = "inner".bar_location_id)
   ->  Index Scan using bar_location_pkey on bar_location  (cost=0.00..344.50 rows=11792 width=610) (actual
time=0.04..59.41rows=11757 loops=1) 
   ->  Sort  (cost=3438.87..3439.37 rows=200 width=44) (actual time=435.51..435.68 rows=112 loops=1)
         Sort Key: "cache".bar_location_id
         ->  Merge Join  (cost=3319.28..3431.22 rows=200 width=44) (actual time=407.23..434.97 rows=112 loops=1)
               Merge Cond: ("outer".bar_id = "inner".bar_id)
               ->  Index Scan using bar_pkey on bar  (cost=0.00..99.52 rows=3768 width=20) (actual time=0.02..18.18
rows=3619loops=1) 
               ->  Sort  (cost=3319.28..3319.78 rows=200 width=20) (actual time=407.17..407.29 rows=112 loops=1)
                     Sort Key: "cache".bar_id
                     ->  Subquery Scan "cache"  (cost=3232.65..3311.64 rows=200 width=20) (actual time=405.71..406.80
rows=112loops=1) 
                           ->  Unique  (cost=3232.65..3311.64 rows=200 width=20) (actual time=405.70..406.24 rows=112
loops=1)
                                 ->  Sort  (cost=3232.65..3272.14 rows=15797 width=20) (actual time=405.69..405.84
rows=146loops=1) 
                                       Sort Key: bar_id, mindist
                                       ->  Subquery Scan x  (cost=1854.57..2131.02 rows=15797 width=20) (actual
time=272.07..405.08rows=146 loops=1) 
                                             ->  GroupAggregate  (cost=1854.57..2131.02 rows=15797 width=20) (actual
time=272.06..404.21rows=146 loops=1) 
                                                   ->  Sort  (cost=1854.57..1894.06 rows=15797 width=20) (actual
time=270.37..289.31rows=16440 loops=1) 
                                                         Sort Key: bar_id, bar_location_id
                                                         ->  Index Scan using idx_cache_foos on cache_foos
(cost=0.00..752.94rows=15797 width=20) (actual time=0.05..118.41 rows=16440 loops=1) 
                                                               Index Cond: ((region_id = 839) AND (dist < 60::double
precision))
                                                               Filter: (foo_id IS NOT NULL)
 Total runtime: 540.00 msec


7.3:
                                                                                            QUERY PLAN
                                                                         

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=2236.97..2892.88 rows=162 width=159) (actual time=515.70..562.77 rows=112 loops=1)
   ->  Merge Join  (cost=2236.97..2397.80 rows=162 width=44) (actual time=515.58..555.70 rows=112 loops=1)
         Merge Cond: ("outer".bar_id = "inner".bar_id)
         ->  Index Scan using bar_pkey on bar  (cost=0.00..148.37 rows=3850 width=20) (actual time=0.05..29.76
rows=3619loops=1) 
         ->  Sort  (cost=2236.97..2237.37 rows=162 width=20) (actual time=515.49..515.64 rows=112 loops=1)
               Sort Key: "cache".bar_id
               ->  Subquery Scan "cache"  (cost=2222.92..2231.02 rows=162 width=20) (actual time=513.96..515.09
rows=112loops=1) 
                     ->  Unique  (cost=2222.92..2231.02 rows=162 width=20) (actual time=513.95..514.51 rows=112
loops=1)
                           ->  Sort  (cost=2222.92..2226.97 rows=1621 width=20) (actual time=513.94..514.11 rows=146
loops=1)
                                 Sort Key: bar_id, mindist
                                 ->  Subquery Scan x  (cost=1933.89..2136.50 rows=1621 width=20) (actual
time=313.72..513.34rows=146 loops=1) 
                                       ->  Aggregate  (cost=1933.89..2136.50 rows=1621 width=20) (actual
time=313.71..512.52rows=146 loops=1) 
                                             ->  Group  (cost=1933.89..2055.46 rows=16209 width=20) (actual
time=311.41..422.29rows=16440 loops=1) 
                                                   ->  Sort  (cost=1933.89..1974.41 rows=16209 width=20) (actual
time=311.39..329.79rows=16440 loops=1) 
                                                         Sort Key: bar_id, bar_location_id
                                                         ->  Index Scan using idx_cache_foos on cache_foos
(cost=0.00..800.51rows=16209 width=20) (actual time=0.05..181.92 rows=16440 loops=1) 
                                                               Index Cond: ((region_id = 839) AND (dist < 60::double
precision))
                                                               Filter: (foo_id IS NOT NULL)
   ->  Index Scan using bar_location_pkey on bar_location  (cost=0.00..3.04 rows=1 width=115) (actual time=0.04..0.04
rows=1loops=112) 
         Index Cond: ("outer".bar_location_id = bar_location.bar_location_id)
 Total runtime: 568.69 msec


[BTW I've had to search and repalce on the plans at the request of the client,
 I hope I didn't lose any relevant information doing that]

--
greg