public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Korotkov <[email protected]>
To: [email protected]
Subject: pgsql: Teach Append to consider tuple_fraction when accumulating subpat
Date: Mon, 10 Mar 2025 11:47:56 +0000
Message-ID: <[email protected]> (raw)
Teach Append to consider tuple_fraction when accumulating subpaths.
This change is dedicated to more active usage of IndexScan and parameterized
NestLoop paths in partitioned cases under an Append node, as it already works
with plain tables. As newly added regression tests demonstrate, it should
provide more smartness to the partitionwise technique.
With an indication of how many tuples are needed, it may be more meaningful
to use the 'fractional branch' subpaths of the Append path list, which are
more optimal for this specific number of tuples. Planning on a higher level,
if the optimizer needs all the tuples, it will choose non-fractional paths.
In the case when, during execution, Append needs to return fewer tuples than
declared by tuple_fraction, it would not be harmful to use the 'intermediate'
variant of paths. However, it will earn a considerable profit if a sensible
set of tuples is selected.
The change of the existing regression test demonstrates the positive outcome
of this feature: instead of scanning the whole table, the optimizer prefers
to use a parameterized scan, being aware of the only single tuple the join
has to produce to perform the query.
Discussion: https://www.postgresql.org/message-id/flat/CAN-LCVPxnWB39CUBTgOQ9O7Dd8DrA_tpT1EY3LNVnUuvAX1NjA%40mai...
Author: Nikita Malakhov <[email protected]>
Author: Andrei Lepikhov <[email protected]>
Reviewed-by: Andy Fan <[email protected]>
Reviewed-by: Alexander Korotkov <[email protected]>
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/fae535da0ac2a8d0bb279cc66d62b0dcc4b5409b
Modified Files
--------------
src/backend/optimizer/path/allpaths.c | 18 ++++-
src/backend/optimizer/plan/planner.c | 8 ++
src/test/regress/expected/partition_join.out | 116 +++++++++++++++++++++++++++
src/test/regress/expected/union.out | 15 ++--
src/test/regress/sql/partition_join.sql | 21 +++++
5 files changed, 168 insertions(+), 10 deletions(-)
reply
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: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pgsql: Teach Append to consider tuple_fraction when accumulating subpat
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox