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

Greg Stark <gsstark@mit.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