2

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
7
  • 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.
    – pifor
    Apr 22, 2020 at 17:52
  • Yes, I noticed that as well, but the explain analyze always has an execution time of ~1/1.5 secs.
    – homer5439
    Apr 22, 2020 at 17:58
  • Also, if it were a caching problem I would expect the query to run faster after a few executions, which doesn't happen instead.
    – homer5439
    Apr 22, 2020 at 18:51
  • 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.
    – jjanes
    Apr 22, 2020 at 19:20
  • 1
    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
    – user1822
    Apr 22, 2020 at 21:47

1 Answer 1

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.

2
  • 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.
    – homer5439
    Apr 22, 2020 at 20:48
  • 1
    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.
    – user1822
    Apr 22, 2020 at 22:02

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.