Date: Sat, 17 Aug 2024 17:35:39 +0200 (CEST)
From: Dimitrios Apostolou <jimis@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-general@lists.postgresql.org
Subject: Re: array_agg() does not stop aggregating according to HAVING
 clause
In-Reply-To: <954894.1723907711@sss.pgh.pa.us>
Message-ID: <3f255b39-e0a7-e04e-0747-c98b13a4e0f6@gmx.net>
References: <215d6efa-bb4b-a76e-6066-7a83ccdb55e3@gmx.net> <954894.1723907711@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII; format=flowed
UI-OutboundReport: notjunk:1;M01:P0:ndy6K+PVOu0=;CB0a8YxpTsYanIpcTBd8lwswjQP
 DUHMCES21if6TQ7vg7Pb1V5r6fBvT6kpogs4Vg9GaT5Rw3zMGWeiNj78vPImz9wT7Ynemadbw
 axGuYlw36DMMn0aKUqIbsB1RiT7eI7qxHH5Ne2nnBZmLsBVW8P5qTtzSJ/qhezSSwgYUzkvCR
 kPQWOlL8SWwgqI0UrlakjRhOD0cuudpgZz12SI7J2fjFVw6hgChE8G/CE+yKeNUc+3d7KUELj
 DSfJ12uaQz1cmM2+el8XKplmo5MW55KJyXKsDNKkLJJD5ZhCG7YnkdVgxwveNYmEdqIIrP10g
 fksx0ENQLUcy+ILTgNJKknhdk29j9wzhwzsyp2OaXVjHkGUlfwXKhp9lCZNl9+3q5MIOn7MFd
 eZFnCyPxREWzuTIAC1ZZlrMTSi4ZzZf9ER25KYzTMUWBMKGvu6PIzVCE2cwExlTL5HyBsImjb
 R9w2KHiuHAUaEgSv1PlOq21jUHWQ+lmdKDdnS0AyMFhlCGupWrY6C/RT4d9DZxSeW9Kh1bz3n
 +LsL02H0WFddNPhM7l80A9OK6u3xfnKz9FI14oooPkvfQSRCLJmkkEZHXwOA05U+4szTvLOSB
 ZXadEGyfdsJXX2d8zYdqAksMy9ALn8SvbZcWmtH7rbJgxYfMHwL7hAWFkCO4frJxWCH0TmMdD
 uR3Yfv9mkUuzOO7tMU3ABtnpq0Lqs5Mgvd4B6xtJlBT6hPsIG3/XNAfIYK1ClD17oS6ir9PzA
 hiaDi0EUFE36/m6922J0VQIAYd7cVpzgP84iEn5Up1gPJMwvFwc6uB2prDwOe7IBCPYTqMinV
 ayx457h4FK8LibiHhhIzM5LA==
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/3f255b39-e0a7-e04e-0747-c98b13a4e0f6%40gmx.net>
Precedence: bulk

On Sat, 17 Aug 2024, Tom Lane wrote:

> Well, yes: the two aggregates (array_agg and count) are computed
> concurrently in a single Aggregate plan node scanning the output
> of the JOIN.  There's no way to apply the HAVING filter until
> after the aggregation is finished.
>
> I think this approach is basically forced by the SQL standard's
> semantics for grouping/aggregation.

FWIW I also tried:

HAVING  array_length(array_agg(run_n), 1) < 10;

but I saw the same amount of temp files, at least in the short duration of
my test run.

Thank you, I will split this into two passes like you suggested. It's just
that I'm doing another 3 passes over this table for different things I
calculate (different GROUP BY, different WHERE clauses) and I was hoping
to minimize the time spent. But avoiding the array_agg() over everything
is my top priority ATM so I'll definitely try.

Regards,
Dimitris