From: Tom Lane <tgl@sss.pgh.pa.us>
To: Neimar Sierota <neimarsmo@gmail.com>
Cc: Mauricio Martini <martini.mauricio@hotmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL uses huge amount of memory
Date: Wed, 08 Mar 2023 13:42:46 -0500
Message-ID: <211479.1678300966@sss.pgh.pa.us> (raw)
In-Reply-To: <CAOX8E_Ro9U6Qvwj8XZHbsxsviuA73RJn_nxaT=yEq2di35MZnA@mail.gmail.com>
References: <MW5PR10MB57391E28809250993B92BB0782B49@MW5PR10MB5739.namprd10.prod.outlook.com>
<CAOX8E_Ro9U6Qvwj8XZHbsxsviuA73RJn_nxaT=yEq2di35MZnA@mail.gmail.com>
Neimar Sierota <neimarsmo@gmail.com> 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@sss.pgh.pa.us>
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@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, neimarsmo@gmail.com, martini.mauricio@hotmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: PostgreSQL uses huge amount of memory
In-Reply-To: <211479.1678300966@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox