Date: Sat, 17 Aug 2024 16:37:25 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: pgsql-general@lists.postgresql.org
Subject: array_agg() does not stop aggregating according to HAVING clause
Message-ID: <215d6efa-bb4b-a76e-6066-7a83ccdb55e3@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset=US-ASCII
UI-OutboundReport: notjunk:1;M01:P0:6fCkWtVl/AY=;nRdiC4fbAK7Pxw30taw8fAoZgr0
 ZdvWgJwK7QmClFa87PHyB47cOrhLREhVtitIHaCHQf46Eg+RuLCjN5B+J0t69cMJmAFt6nSyu
 cWBRAZVXcvnZ31DXZM3i2GuvN1DHHny93inQvqjFVZBhuPOoDvJo/b8zT10KkdOoab8PSoiM4
 8/eeVqfZ5rzphW1+jr3HWNmNjreSBge4eEJUrKjIMpBRbX/Jo+uFgWOQ4xQpqsgzkP0awpDDx
 mfMVdnj8Q2pA6aWK/TKZZ7HMlurvmkHggTcLIo3uQxoZtaoNyIeu1FJ3Y6AAhFt/srdtUG+M8
 mRQDCOjPKwnzJDrr8qd/gKtVPgljCBBNJm3n5TSpKLUyG8Xjy3lzBY3mQd6VnzYuFrT4CggmS
 HsOH/hz984UOpn+vKFKF6rulTKE+SIcVizMnAYtqyk5mvPIDn5qUDyPnBy9DuShoC3lMRac6S
 JZLFSGFZcdSVcdPUdlkocTrO82peUAPBlR+LyeRGtjferpKijRTlwgLSGM0R6gqLQ25WmOfjN
 PC4Z648eqj3KwOJFUZE1Su7KvHFSTElqA7ePX3SOjTGrsmyo8XlYfUPWqHY0T/a36WRlxSoHs
 nneuI0xPJ1wGdUhwksxmTb49yAK8afdPL+TSu9YQBGuh+Fu5h4KYiN4CrYOMT7LTEBmlKkfGU
 KNO5Aq2xt6/w3oQMyRTFT3HrA2kvWaaAPvYXHoEAezi/x7dbQJIHFyYw4rO2zYhHg6Zktk0+z
 BnWg33UFLAnSVT1Wx96UJ05UCs49lLvo/aigOOELLchuxuWwTwFOR1YlB5Frj6dKdBtH7x4lm
 V6NicR0sahAQQeDpUvBTD6/A==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/215d6efa-bb4b-a76e-6066-7a83ccdb55e3%40gmx.net>
Precedence: bulk

Hello list,

I have a query that goes through *billions* of rows and for the columns
that have an infrequent "datatag" (HAVING count(test_datatag_n)<10) it
selects all the IDs of the entries (array_agg(run_n)). Here is the full
query:


INSERT INTO infrequent_datatags_in_this_chunk
   SELECT datatag, datatags.datatag_n, array_agg(run_n)
     FROM runs_raw
     JOIN datatags USING(datatag_n)
     WHERE workitem_n >=3D 295
       AND workitem_n <  714218
       AND datatag IS NOT NULL
     GROUP BY     datatags.datatag_n
     HAVING  count(datatag_n) < 10
       AND   count(datatag_n) > 0  -- Not really needed because of the JOI=
N above
;

The runs_raw table has run_n as the primary key id, and an index on
workitem_n. The datatags table is a key value store with datatag_n as
primary key.

The problem is that this is extremely slow (5 hours), most likely because
it creates tens of gigabytes of temporary files as I see in the logs. I
suspect that it is writing to disk the array_agg(run_n) of all entries and
not only those HAVING count(datatag_n)<10. (I might be wrong though, as
this is only an assumption based on the amount of data written; I don't
know of any way to examine the temporary files written). While this query
is going through billions of rows, the ones with infrequent datatags are
maybe 10M.

How do I tell postgres to stop aggregating when count>=3D10?

Thank you in advance,
Dimitris