Thread: pgsql: Teach Append to consider tuple_fraction when accumulating subpat
pgsql: Teach Append to consider tuple_fraction when accumulating subpat
From
Alexander Korotkov
Date:
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%40mail.gmail.com Author: Nikita Malakhov <hukutoc@gmail.com> Author: Andrei Lepikhov <lepihov@gmail.com> Reviewed-by: Andy Fan <zhihuifan1213@163.com> Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com> 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(-)