Neimar Sierota <neimarsmo@xxxxxxxxx> writes:
> I did a test as you mentioned and in postgresql version 11 I didn't observe
> any change in memory consumption while the query is executed. With version
> 14, running the same query, it is possible to observe a considerable
> increase in memory usage during execution.
I poked into this a little bit. With a query like
SELECT 0 AS x
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
...
UNION ALL SELECT 9999
UNION ALL SELECT 10000
;
it is possible to see a jump in memory usage between v11 and v12.
On the other hand, if the UNION arms aren't quite so trivial, say
CREATE TABLE dual AS SELECT 1 AS y;
SELECT 0 AS x FROM dual
UNION ALL SELECT 1 FROM dual
UNION ALL SELECT 2 FROM dual
UNION ALL SELECT 3 FROM dual
...
UNION ALL SELECT 9999 FROM dual
UNION ALL SELECT 10000 FROM dual
;
both branches are equally bad :-(, consuming about O(N^2) memory
and time during planning. I bisected the behavior change to
commit 4be058fe9ec5e630239b656af21fc083371f30ed
Author: Tom Lane <tgl@xxxxxxxxxxxxx>
Date: Mon Jan 28 17:54:10 2019 -0500
In the planner, replace an empty FROM clause with a dummy RTE.
which was an intentional change to allow empty-FROM-clause SELECTs
to be optimized on the same basis as SELECTs with normal FROM clauses.
The problem is that subquery flattening, which didn't happen at all
with the first type of query in v11, is eating a lot of resources
when there are a lot of UNION arms.
The good news is that in HEAD, both shapes of query are fast,
which happened at
commit e42e312430279dcd8947846fdfeb4885e3754eac
Author: Tom Lane <tgl@xxxxxxxxxxxxx>
Date: Thu Dec 22 11:02:03 2022 -0500
Avoid O(N^2) cost when pulling up lots of UNION ALL subqueries.
I doubt we'd risk back-patching that, but at least a solution is
in the offing.
regards, tom lane
