Re: Is Hash Agg being used? 7.4 seems to handle this query worse than 7.3

Greg Stark <gsstark(at)mit(dot)edu> writes:
> Hm, CVS doesn't seem to be using a hash aggregate. At least, if it is it isn't
> obvious from the plan.

> 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

Why would you expect hash aggregation to be used here? There's no
aggregates ... nor even any GROUP BY.

A hash aggregation plan looks like this:

regression=# explain select ten, sum(unique1) from tenk1 group by ten;
QUERY PLAN
-----------------------------------------------------------------
HashAggregate (cost=508.00..508.02 rows=10 width=8)
-> Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=8)
(2 rows)

regards, tom lane