Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfae535d

Browse files
committed
Teach Append to consider tuple_fraction when accumulating subpaths.
This change is dedicated to more active usage of IndexScan and parameterizedNestLoop paths in partitioned cases under an Append node, as it already workswith plain tables. As newly added regression tests demonstrate, it shouldprovide more smartness to the partitionwise technique.With an indication of how many tuples are needed, it may be more meaningfulto use the 'fractional branch' subpaths of the Append path list, which aremore 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 thandeclared by tuple_fraction, it would not be harmful to use the 'intermediate'variant of paths. However, it will earn a considerable profit if a sensibleset of tuples is selected.The change of the existing regression test demonstrates the positive outcomeof this feature: instead of scanning the whole table, the optimizer prefersto use a parameterized scan, being aware of the only single tuple the joinhas to produce to perform the query.Discussion:https://www.postgresql.org/message-id/flat/CAN-LCVPxnWB39CUBTgOQ9O7Dd8DrA_tpT1EY3LNVnUuvAX1NjA%40mail.gmail.comAuthor: 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>
1 parentb83e8a2 commitfae535d

File tree

5 files changed

+168
-10
lines changed

5 files changed

+168
-10
lines changed

‎src/backend/optimizer/path/allpaths.c

Lines changed: 16 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1371,9 +1371,23 @@ add_paths_to_append_rel(PlannerInfo *root, RelOptInfo *rel,
13711371
*/
13721372
if (rel->consider_startup&&childrel->cheapest_startup_path!=NULL)
13731373
{
1374+
Path*cheapest_path;
1375+
1376+
/*
1377+
* With an indication of how many tuples the query should provide,
1378+
* the optimizer tries to choose the path optimal for that
1379+
* specific number of tuples.
1380+
*/
1381+
if (root->tuple_fraction>0.0)
1382+
cheapest_path=
1383+
get_cheapest_fractional_path(childrel,
1384+
root->tuple_fraction);
1385+
else
1386+
cheapest_path=childrel->cheapest_startup_path;
1387+
13741388
/* cheapest_startup_path must not be a parameterized path. */
1375-
Assert(childrel->cheapest_startup_path->param_info==NULL);
1376-
accumulate_append_subpath(childrel->cheapest_startup_path,
1389+
Assert(cheapest_path->param_info==NULL);
1390+
accumulate_append_subpath(cheapest_path,
13771391
&startup_subpaths,
13781392
NULL);
13791393
}

‎src/backend/optimizer/plan/planner.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6414,6 +6414,11 @@ make_sort_input_target(PlannerInfo *root,
64146414
* Find the cheapest path for retrieving a specified fraction of all
64156415
* the tuples expected to be returned by the given relation.
64166416
*
6417+
* Do not consider parameterized paths. If the caller needs a path for upper
6418+
* rel, it can't have parameterized paths. If the caller needs an append
6419+
* subpath, it could become limited by the treatment of similar
6420+
* parameterization of all the subpaths.
6421+
*
64176422
* We interpret tuple_fraction the same way as grouping_planner.
64186423
*
64196424
* We assume set_cheapest() has been run on the given rel.
@@ -6436,6 +6441,9 @@ get_cheapest_fractional_path(RelOptInfo *rel, double tuple_fraction)
64366441
{
64376442
Path*path= (Path*)lfirst(l);
64386443

6444+
if (path->param_info)
6445+
continue;
6446+
64396447
if (path==rel->cheapest_total_path||
64406448
compare_fractional_path_costs(best_path,path,tuple_fraction) <=0)
64416449
continue;

‎src/test/regress/expected/partition_join.out

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5260,6 +5260,122 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id DE
52605260
Index Cond: (id = x_2.id)
52615261
(11 rows)
52625262

5263+
--
5264+
-- Test Append's fractional paths
5265+
--
5266+
CREATE INDEX pht1_c_idx ON pht1(c);
5267+
-- SeqScan might be the best choice if we need one single tuple
5268+
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1;
5269+
QUERY PLAN
5270+
--------------------------------------------------
5271+
Limit
5272+
-> Append
5273+
-> Nested Loop
5274+
Join Filter: (p1_1.c = p2_1.c)
5275+
-> Seq Scan on pht1_p1 p1_1
5276+
-> Materialize
5277+
-> Seq Scan on pht1_p1 p2_1
5278+
-> Nested Loop
5279+
Join Filter: (p1_2.c = p2_2.c)
5280+
-> Seq Scan on pht1_p2 p1_2
5281+
-> Materialize
5282+
-> Seq Scan on pht1_p2 p2_2
5283+
-> Nested Loop
5284+
Join Filter: (p1_3.c = p2_3.c)
5285+
-> Seq Scan on pht1_p3 p1_3
5286+
-> Materialize
5287+
-> Seq Scan on pht1_p3 p2_3
5288+
(17 rows)
5289+
5290+
-- Increase number of tuples requested and an IndexScan will be chosen
5291+
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
5292+
QUERY PLAN
5293+
------------------------------------------------------------------------
5294+
Limit
5295+
-> Append
5296+
-> Nested Loop
5297+
-> Seq Scan on pht1_p1 p1_1
5298+
-> Memoize
5299+
Cache Key: p1_1.c
5300+
Cache Mode: logical
5301+
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
5302+
Index Cond: (c = p1_1.c)
5303+
-> Nested Loop
5304+
-> Seq Scan on pht1_p2 p1_2
5305+
-> Memoize
5306+
Cache Key: p1_2.c
5307+
Cache Mode: logical
5308+
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
5309+
Index Cond: (c = p1_2.c)
5310+
-> Nested Loop
5311+
-> Seq Scan on pht1_p3 p1_3
5312+
-> Memoize
5313+
Cache Key: p1_3.c
5314+
Cache Mode: logical
5315+
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
5316+
Index Cond: (c = p1_3.c)
5317+
(23 rows)
5318+
5319+
-- If almost all the data should be fetched - prefer SeqScan
5320+
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 1000;
5321+
QUERY PLAN
5322+
--------------------------------------------------
5323+
Limit
5324+
-> Append
5325+
-> Hash Join
5326+
Hash Cond: (p1_1.c = p2_1.c)
5327+
-> Seq Scan on pht1_p1 p1_1
5328+
-> Hash
5329+
-> Seq Scan on pht1_p1 p2_1
5330+
-> Hash Join
5331+
Hash Cond: (p1_2.c = p2_2.c)
5332+
-> Seq Scan on pht1_p2 p1_2
5333+
-> Hash
5334+
-> Seq Scan on pht1_p2 p2_2
5335+
-> Hash Join
5336+
Hash Cond: (p1_3.c = p2_3.c)
5337+
-> Seq Scan on pht1_p3 p1_3
5338+
-> Hash
5339+
-> Seq Scan on pht1_p3 p2_3
5340+
(17 rows)
5341+
5342+
SET max_parallel_workers_per_gather = 1;
5343+
SET debug_parallel_query = on;
5344+
-- Partial paths should also be smart enough to employ limits
5345+
EXPLAIN (COSTS OFF) SELECT * FROM pht1 p1 JOIN pht1 p2 USING (c) LIMIT 100;
5346+
QUERY PLAN
5347+
------------------------------------------------------------------------------
5348+
Gather
5349+
Workers Planned: 1
5350+
Single Copy: true
5351+
-> Limit
5352+
-> Append
5353+
-> Nested Loop
5354+
-> Seq Scan on pht1_p1 p1_1
5355+
-> Memoize
5356+
Cache Key: p1_1.c
5357+
Cache Mode: logical
5358+
-> Index Scan using pht1_p1_c_idx on pht1_p1 p2_1
5359+
Index Cond: (c = p1_1.c)
5360+
-> Nested Loop
5361+
-> Seq Scan on pht1_p2 p1_2
5362+
-> Memoize
5363+
Cache Key: p1_2.c
5364+
Cache Mode: logical
5365+
-> Index Scan using pht1_p2_c_idx on pht1_p2 p2_2
5366+
Index Cond: (c = p1_2.c)
5367+
-> Nested Loop
5368+
-> Seq Scan on pht1_p3 p1_3
5369+
-> Memoize
5370+
Cache Key: p1_3.c
5371+
Cache Mode: logical
5372+
-> Index Scan using pht1_p3_c_idx on pht1_p3 p2_3
5373+
Index Cond: (c = p1_3.c)
5374+
(26 rows)
5375+
5376+
RESET debug_parallel_query;
5377+
-- Remove indexes from the partitioned table and its partitions
5378+
DROP INDEX pht1_c_idx CASCADE;
52635379
-- cleanup
52645380
DROP TABLE fract_t;
52655381
RESET max_parallel_workers_per_gather;

‎src/test/regress/expected/union.out

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1472,18 +1472,17 @@ select t1.unique1 from tenk1 t1
14721472
inner join tenk2 t2 on t1.tenthous = t2.tenthous and t2.thousand = 0
14731473
union all
14741474
(values(1)) limit 1;
1475-
QUERY PLAN
1476-
--------------------------------------------------------
1475+
QUERY PLAN
1476+
---------------------------------------------------------------------
14771477
Limit
14781478
-> Append
14791479
-> Nested Loop
1480-
Join Filter: (t1.tenthous = t2.tenthous)
1481-
-> Seq Scan on tenk1 t1
1482-
-> Materialize
1483-
-> Seq Scan on tenk2 t2
1484-
Filter: (thousand = 0)
1480+
-> Seq Scan on tenk2 t2
1481+
Filter: (thousand = 0)
1482+
-> Index Scan using tenk1_thous_tenthous on tenk1 t1
1483+
Index Cond: (tenthous = t2.tenthous)
14851484
-> Result
1486-
(9 rows)
1485+
(8 rows)
14871486

14881487
-- Ensure there is no problem if cheapest_startup_path is NULL
14891488
explain (costs off)

‎src/test/regress/sql/partition_join.sql

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1225,6 +1225,27 @@ SELECT x.id, y.id FROM fract_t x LEFT JOIN fract_t y USING (id) ORDER BY x.id AS
12251225
EXPLAIN (COSTS OFF)
12261226
SELECTx.id,y.idFROM fract_t xLEFT JOIN fract_t y USING (id)ORDER BYx.idDESCLIMIT10;
12271227

1228+
--
1229+
-- Test Append's fractional paths
1230+
--
1231+
1232+
CREATEINDEXpht1_c_idxON pht1(c);
1233+
-- SeqScan might be the best choice if we need one single tuple
1234+
EXPLAIN (COSTS OFF)SELECT*FROM pht1 p1JOIN pht1 p2 USING (c)LIMIT1;
1235+
-- Increase number of tuples requested and an IndexScan will be chosen
1236+
EXPLAIN (COSTS OFF)SELECT*FROM pht1 p1JOIN pht1 p2 USING (c)LIMIT100;
1237+
-- If almost all the data should be fetched - prefer SeqScan
1238+
EXPLAIN (COSTS OFF)SELECT*FROM pht1 p1JOIN pht1 p2 USING (c)LIMIT1000;
1239+
1240+
SET max_parallel_workers_per_gather=1;
1241+
SET debug_parallel_query=on;
1242+
-- Partial paths should also be smart enough to employ limits
1243+
EXPLAIN (COSTS OFF)SELECT*FROM pht1 p1JOIN pht1 p2 USING (c)LIMIT100;
1244+
RESET debug_parallel_query;
1245+
1246+
-- Remove indexes from the partitioned table and its partitions
1247+
DROPINDEX pht1_c_idx CASCADE;
1248+
12281249
-- cleanup
12291250
DROPTABLE fract_t;
12301251

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp