1

I have a query with a group by that either performs a sort followed by group aggregate or a hash aggregate. I noticed that when adding an array_agg function, the query never tries to perform a hash aggregate increasing the query time. I have set my work_mem for the transaction to a really high number so there is no memory constraint.

Does postgres never use hashaggregate if there is an array_agg function in the aggregation?

This is the format of the query in question:

SELECT
    "test"."db"."a",
    "test"."db"."b",
    "test"."db"."c",
    "test"."db"."d",
    "test"."db"."e",
    "test"."db"."f",
    "test"."db"."g",
    array_agg(ARRAY["test"."db"."resource_type", "test"."db"."resource_name", "test"."db"."pattern_type"]) FILTER (WHERE "test"."db"."deleted" is FALSE) AS "resources"
FROM
    "test"."db"
WHERE ("test"."db"."last_change_id" BETWEEN 0 AND val3
    AND "test"."db"."c" = 'val1'
    AND "test"."db"."d" = 'val2')
GROUP BY
    "test"."db"."a",
    "test"."db"."b",
    "test"."db"."c",
    "test"."db"."d",
    "test"."db"."e",
    "test"."db"."f",
    "test"."db"."g"

The plan executed is the following:

GroupAggregate  (cost=180461.63..183740.70 rows=84788 width=147) (actual time=3310.049..3764.216 rows=119173 loops=1)
   Group Key: a, b, c, d, e, f, g
   ->  Sort  (cost=180461.63..180708.21 rows=98632 width=151) (actual time=3310.030..3394.925 rows=513793 loops=1)
         Sort Key: a, b, e, f, g
         Sort Method: quicksort  Memory: 148765kB
         ->  Gather  (cost=1000.00..172280.22 rows=98632 width=151) (actual time=2.590..478.026 rows=513793 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Parallel Seq Scan on role_binding  (cost=0.00..161417.02 rows=41097 width=151) (actual time=0.897..414.638 rows=171264 loops=3)
                     Filter: ((last_change_id >= 0) AND (last_change_id <= val3) AND (c = 'val1'::text) AND (d = 'val2'::text))
                     Rows Removed by Filter: 826426
 Planning Time: 0.181 ms
 Execution Time: 3782.427 ms
8
  • Is there an ORDER BY clause in the aggregate function? Commented Jan 9 at 15:48
  • Perhaps you could add the queries and their respective plans to the question?
    – mustaccio
    Commented Jan 9 at 16:15
  • It can use a hash aggregate with array_agg. Knowing why it doesn't in one particular case would depend on the details of the case, and maybe the version.
    – jjanes
    Commented Jan 9 at 16:16
  • There is no order by in the aggregate function
    – Major Ben
    Commented Jan 9 at 16:34
  • I pasted the sample query and execution plan. I had to change some names around but basically the moment I remove the array_agg I see that it uses a hash aggregate in the query plan
    – Major Ben
    Commented Jan 9 at 16:53

1 Answer 1

1

In my hands in 12.9 it uses the hash agg as long as the work_mem is large enough that it is confident the data will fit in memory. Without having your actual data, I don't know what large enough would mean for you, but a few Gig would probably do it.

In later versions, hash agg was taught to spill to disk, which makes the planner more eager to use it.

2
  • I set work_mem to 512MB, and the sort used 125MB.
    – Major Ben
    Commented Jan 9 at 21:05
  • I don't know if that means that it worked, or it didn't work.
    – jjanes
    Commented Jan 9 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.