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

Commit773db22

Browse files
author
Richard Guo
committed
Suppress unnecessary explicit sorting for EPQ mergejoin path
When building a ForeignPath for a joinrel, if there's a possibilitythat EvalPlanQual will be executed, we must identify a suitable pathfor EPQ checks. If the outer or inner path of the chosen path is aForeignPath representing a pushed-down join, we replace it with itsfdw_outerpath to ensure that the EPQ check path consists entirely oflocal joins.If the chosen path is a MergePath, and its outer or inner path is aForeignPath that is not already well enough ordered, the MergePathwill have non-NIL outersortkeys or innersortkeys indicating thedesired ordering to be created by an explicit Sort node. If we thenreplace the outer or inner path with its corresponding fdw_outerpath,and that path is already sufficiently ordered, we end up in aninconsistent state: the MergePath has non-NIL outersortkeys orinnersortkeys, and its input path is already properly ordered. Thisinconsistency can result in an Assert failure or the addition of aredundant Sort node.To fix, check if the new outer or inner path of a MergePath is alreadyproperly sorted, and set its outersortkeys or innersortkeys to NIL ifso.Bug: #18902Reported-by: Nikita Kalinin <n.kalinin@postgrespro.ru>Author: Richard Guo <guofenglinux@gmail.com>Reviewed-by: Tender Wang <tndrwang@gmail.com>Discussion:https://postgr.es/m/18902-71c1bed2b9f7c46f@postgresql.org
1 parent9fef27a commit773db22

File tree

3 files changed

+102
-0
lines changed

3 files changed

+102
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2565,6 +2565,70 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
25652565
96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006 | 6 | 7 | AAA006 | 6 | 6 | 0006
25662566
(10 rows)
25672567

2568+
EXPLAIN (VERBOSE, COSTS OFF)
2569+
SELECT * FROM ft1, ft4, ft5, local_tbl WHERE ft1.c1 = ft4.c1 AND ft1.c1 = ft5.c1
2570+
AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft5.c1 < 100 FOR UPDATE;
2571+
QUERY PLAN
2572+
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2573+
LockRows
2574+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3, local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.*, ft4.*, ft5.*, local_tbl.ctid
2575+
-> Merge Join
2576+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3, local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.*, ft4.*, ft5.*, local_tbl.ctid
2577+
Merge Cond: (local_tbl.c1 = ft1.c2)
2578+
-> Index Scan using local_tbl_pkey on public.local_tbl
2579+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
2580+
-> Sort
2581+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
2582+
Sort Key: ft1.c2
2583+
-> Foreign Scan
2584+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
2585+
Relations: ((public.ft1) INNER JOIN (public.ft4)) INNER JOIN (public.ft5)
2586+
Remote SQL: SELECT r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8, CASE WHEN (r1.*)::text IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7, r1.c8) END, r2.c1, r2.c2, r2.c3, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2, r2.c3) END, r3.c1, r3.c2, r3.c3, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.c1, r3.c2, r3.c3) END FROM (("S 1"."T 1" r1 INNER JOIN "S 1"."T 3" r2 ON (((r1."C 1" = r2.c1)) AND ((r1."C 1" < 100)))) INNER JOIN "S 1"."T 4" r3 ON (((r1."C 1" = r3.c1)) AND ((r3.c1 < 100)))) FOR UPDATE OF r1 FOR UPDATE OF r2 FOR UPDATE OF r3
2587+
-> Merge Join
2588+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft4.c1, ft4.c2, ft4.c3, ft4.*, ft5.c1, ft5.c2, ft5.c3, ft5.*
2589+
Merge Cond: (ft1.c1 = ft5.c1)
2590+
-> Merge Join
2591+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft4.c1, ft4.c2, ft4.c3, ft4.*
2592+
Merge Cond: (ft1.c1 = ft4.c1)
2593+
-> Sort
2594+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
2595+
Sort Key: ft1.c1
2596+
-> Foreign Scan on public.ft1
2597+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
2598+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100)) FOR UPDATE
2599+
-> Sort
2600+
Output: ft4.c1, ft4.c2, ft4.c3, ft4.*
2601+
Sort Key: ft4.c1
2602+
-> Foreign Scan on public.ft4
2603+
Output: ft4.c1, ft4.c2, ft4.c3, ft4.*
2604+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 3" FOR UPDATE
2605+
-> Sort
2606+
Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
2607+
Sort Key: ft5.c1
2608+
-> Foreign Scan on public.ft5
2609+
Output: ft5.c1, ft5.c2, ft5.c3, ft5.*
2610+
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4" WHERE ((c1 < 100)) FOR UPDATE
2611+
(38 rows)
2612+
2613+
SELECT * FROM ft1, ft4, ft5, local_tbl WHERE ft1.c1 = ft4.c1 AND ft1.c1 = ft5.c1
2614+
AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft5.c1 < 100 FOR UPDATE;
2615+
c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 | c1 | c2 | c3 | c1 | c2 | c3 | c1 | c2 | c3
2616+
----+----+-------+------------------------------+--------------------------+----+------------+-----+----+----+--------+----+----+--------+----+----+------
2617+
12 | 2 | 00012 | Tue Jan 13 00:00:00 1970 PST | Tue Jan 13 00:00:00 1970 | 2 | 2 | foo | 12 | 13 | AAA012 | 12 | 13 | AAA012 | 2 | 2 | 0002
2618+
42 | 2 | 00042 | Thu Feb 12 00:00:00 1970 PST | Thu Feb 12 00:00:00 1970 | 2 | 2 | foo | 42 | 43 | AAA042 | 42 | 43 | AAA042 | 2 | 2 | 0002
2619+
72 | 2 | 00072 | Sat Mar 14 00:00:00 1970 PST | Sat Mar 14 00:00:00 1970 | 2 | 2 | foo | 72 | 73 | AAA072 | 72 | 73 | | 2 | 2 | 0002
2620+
24 | 4 | 00024 | Sun Jan 25 00:00:00 1970 PST | Sun Jan 25 00:00:00 1970 | 4 | 4 | foo | 24 | 25 | AAA024 | 24 | 25 | AAA024 | 4 | 4 | 0004
2621+
54 | 4 | 00054 | Tue Feb 24 00:00:00 1970 PST | Tue Feb 24 00:00:00 1970 | 4 | 4 | foo | 54 | 55 | AAA054 | 54 | 55 | | 4 | 4 | 0004
2622+
84 | 4 | 00084 | Thu Mar 26 00:00:00 1970 PST | Thu Mar 26 00:00:00 1970 | 4 | 4 | foo | 84 | 85 | AAA084 | 84 | 85 | AAA084 | 4 | 4 | 0004
2623+
96 | 6 | 00096 | Tue Apr 07 00:00:00 1970 PST | Tue Apr 07 00:00:00 1970 | 6 | 6 | foo | 96 | 97 | AAA096 | 96 | 97 | AAA096 | 6 | 6 | 0006
2624+
36 | 6 | 00036 | Fri Feb 06 00:00:00 1970 PST | Fri Feb 06 00:00:00 1970 | 6 | 6 | foo | 36 | 37 | AAA036 | 36 | 37 | | 6 | 6 | 0006
2625+
66 | 6 | 00066 | Sun Mar 08 00:00:00 1970 PST | Sun Mar 08 00:00:00 1970 | 6 | 6 | foo | 66 | 67 | AAA066 | 66 | 67 | AAA066 | 6 | 6 | 0006
2626+
6 | 6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6 | 6 | foo | 6 | 7 | AAA006 | 6 | 7 | AAA006 | 6 | 6 | 0006
2627+
48 | 8 | 00048 | Wed Feb 18 00:00:00 1970 PST | Wed Feb 18 00:00:00 1970 | 8 | 8 | foo | 48 | 49 | AAA048 | 48 | 49 | AAA048 | 8 | 8 | 0008
2628+
18 | 8 | 00018 | Mon Jan 19 00:00:00 1970 PST | Mon Jan 19 00:00:00 1970 | 8 | 8 | foo | 18 | 19 | AAA018 | 18 | 19 | | 8 | 8 | 0008
2629+
78 | 8 | 00078 | Fri Mar 20 00:00:00 1970 PST | Fri Mar 20 00:00:00 1970 | 8 | 8 | foo | 78 | 79 | AAA078 | 78 | 79 | AAA078 | 8 | 8 | 0008
2630+
(13 rows)
2631+
25682632
RESET enable_nestloop;
25692633
RESET enable_hashjoin;
25702634
-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -715,6 +715,11 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
715715
ANDft1.c2=ft5.c1ANDft1.c2=local_tbl.c1ANDft1.c1<100ANDft2.c1<100 FORUPDATE;
716716
SELECT*FROM ft1, ft2, ft4, ft5, local_tblWHEREft1.c1=ft2.c1ANDft1.c2=ft4.c1
717717
ANDft1.c2=ft5.c1ANDft1.c2=local_tbl.c1ANDft1.c1<100ANDft2.c1<100 FORUPDATE;
718+
EXPLAIN (VERBOSE, COSTS OFF)
719+
SELECT*FROM ft1, ft4, ft5, local_tblWHEREft1.c1=ft4.c1ANDft1.c1=ft5.c1
720+
ANDft1.c2=local_tbl.c1ANDft1.c1<100ANDft5.c1<100 FORUPDATE;
721+
SELECT*FROM ft1, ft4, ft5, local_tblWHEREft1.c1=ft4.c1ANDft1.c1=ft5.c1
722+
ANDft1.c2=local_tbl.c1ANDft1.c1<100ANDft5.c1<100 FORUPDATE;
718723
RESET enable_nestloop;
719724
RESET enable_hashjoin;
720725

‎src/backend/foreign/foreign.c

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22,6 +22,7 @@
2222
#include"foreign/foreign.h"
2323
#include"funcapi.h"
2424
#include"miscadmin.h"
25+
#include"optimizer/paths.h"
2526
#include"tcop/tcopprot.h"
2627
#include"utils/builtins.h"
2728
#include"utils/memutils.h"
@@ -808,7 +809,23 @@ GetExistingLocalJoinPath(RelOptInfo *joinrel)
808809

809810
foreign_path= (ForeignPath*)joinpath->outerjoinpath;
810811
if (IS_JOIN_REL(foreign_path->path.parent))
812+
{
811813
joinpath->outerjoinpath=foreign_path->fdw_outerpath;
814+
815+
if (joinpath->path.pathtype==T_MergeJoin)
816+
{
817+
MergePath*merge_path= (MergePath*)joinpath;
818+
819+
/*
820+
* If the new outer path is already well enough ordered
821+
* for the mergejoin, we can skip doing an explicit sort.
822+
*/
823+
if (merge_path->outersortkeys&&
824+
pathkeys_contained_in(merge_path->outersortkeys,
825+
joinpath->outerjoinpath->pathkeys))
826+
merge_path->outersortkeys=NIL;
827+
}
828+
}
812829
}
813830

814831
if (IsA(joinpath->innerjoinpath,ForeignPath))
@@ -817,7 +834,23 @@ GetExistingLocalJoinPath(RelOptInfo *joinrel)
817834

818835
foreign_path= (ForeignPath*)joinpath->innerjoinpath;
819836
if (IS_JOIN_REL(foreign_path->path.parent))
837+
{
820838
joinpath->innerjoinpath=foreign_path->fdw_outerpath;
839+
840+
if (joinpath->path.pathtype==T_MergeJoin)
841+
{
842+
MergePath*merge_path= (MergePath*)joinpath;
843+
844+
/*
845+
* If the new inner path is already well enough ordered
846+
* for the mergejoin, we can skip doing an explicit sort.
847+
*/
848+
if (merge_path->innersortkeys&&
849+
pathkeys_contained_in(merge_path->innersortkeys,
850+
joinpath->innerjoinpath->pathkeys))
851+
merge_path->innersortkeys=NIL;
852+
}
853+
}
821854
}
822855

823856
return (Path*)joinpath;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp