Very slow postgresql aggregation

Question

EDIT: docker image for testing with the same data (obfuscated)

$ docker run --rm --name pg -d homer5439/pgagg
$ docker exec -ti pg bash
# createdb -U postgres test; zcat /tmp/corr.sql.gz | psql -U postgres test
# psql -U postgres test

and you can run the examples below.

---

I was wondering why the following query takes more than 30 seconds on postgresql 12.2:

SELECT
 contract_id,
 array_agg(corr) AS corr
 FROM
 corr
 GROUP BY contract_id;

More info: the corr table has about 150000 records. Each record has the following structure:

gse=# \d corr ;
 Table "public.corr"
 Column | Type | Collation | Nullable | Default 
-------------+---------+-----------+----------+-----------------------------------------------------------------
 corr_id | integer | | not null | nextval('corr_corr_id_seq'::regclass)
 contract_id | integer | | | 
 start_date | date | | | 
 corr_type | text | | | 
 descr | text | | | 
 credit | numeric | | | 
 detail | text | | | 
Indexes:
 "corr_pkey" PRIMARY KEY, btree (corr_id)
 "corr_contract_id_idx" btree (contract_id)

The detail field contains text up to 2/3 MB in length (about 10% of the records have detail that long; the others have some (~10-20) kilobytes). The number of distinct contract_id values is currently 2317.

Following other suggestions found on this site, I've tried changing the value of work_mem up to 10GB without noticeable changes.

Here's the query plan:

 QUERY PLAN 
---------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=9883.29..9911.57 rows=2263 width=36) (actual time=1184.971..1357.309 rows=2317 loops=1)
 Output: contract_id, array_agg(corr.*)
 Group Key: corr.contract_id
 Buffers: shared hit=78012 read=49899
 -> Seq Scan on public.corr (cost=0.00..9320.19 rows=112619 width=571 (actual time=0.057..959.359 rows=112619 loops=1)
 Output: contract_id, corr.*
 Buffers: shared hit=78012 read=49899
Planning Time: 0.131 ms
Execution Time: 1357.747 ms

• pifor: In the given execution plan query takes only 1,3 seconds and likely because about 40% of the table data is not in the database cache. If you have a execution plan with 30 seconds, please post it.
• homer5439: Yes, I noticed that as well, but the explain analyze always has an execution time of ~1/1.5 secs.
• homer5439: Also, if it were a caching problem I would expect the query to run faster after a few executions, which doesn't happen instead.
• jjanes: If this query always takes 1 to 1.5 seconds, then what is it that is taking 30 seconds? Maybe your client or network are the issue.
• user1822: Your aggregation of the details column ends up being about ~1.8GB of data - I wonder what you are going to do with that data, once you have it. But you probably can't do very much about it. I guess this is an operation that simply isn't as optimized as you would need it to be

Answer 1

When you running "alone", if you mean running it in "psql" but just without the EXPLAIN ANALYZE, then "psql" is reading the entire result set into memory, and going over that set deciding how long the longest entry is for each column so that it can format it to that length. That can be slow, though I would not expect 30 seconds for 2317 lines!

Except I see you don't have a "corr" column in your "corr" table, so what you are aggregating is the entire row records, which you already said has a column that can be very wide. So you are slinging a huge amount of data at psql, and it takes a long time to process it, plus maybe doing some swap.

• homer5439: Interestingly, using array_agg(detail) takes the same time to run the plain query, but the explain analyze time jumps to about 4 seconds. I'm trying to set up a docker image for public testing.
• user1822: Strangely enough, when I run explain analyze for array_agg(corr) (aggregating the whole row) on my computer it's quite fast: less then 500ms. When I run array_agg(details) (the text column) it takes 3.5 seconds.