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

Commit02a568a

Browse files
committed
postgres_fdw: Fix the fix for crash when pushing down multiple joins.
Commit3151f16 was intended to bea commit of a patch from Ashutosh Bapat, but instead I mistakenlycommitted an earlier version from Michael Paquier (because bothpatches were submitted with the same filename, and I confused them).Michael's patch fixes the crash but doesn't actually implement thecorrect test.Repair the incorrect logic, and also expand the comments considerablyso that this is all more clear.Ashutosh Bapat and Robert Haas
1 parent1b812af commit02a568a

File tree

3 files changed

+110
-4
lines changed

3 files changed

+110
-4
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,6 +478,74 @@ SELECT t1."C 1" FROM "S 1"."T 1" t1 left join ft1 t2 join ft2 t3 on (t2.c1 = t3.
478478
110
479479
(10 rows)
480480

481+
-- Test similar to above, except that the full join prevents any equivalence
482+
-- classes from being merged. This produces single relation equivalence classes
483+
-- included in join restrictions.
484+
EXPLAIN (COSTS false, VERBOSE)
485+
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
486+
QUERY PLAN
487+
------------------------------------------------------------------------------------------------------------------------------------------------------------------
488+
Limit
489+
Output: t1."C 1", t2.c1, t3.c1
490+
-> Merge Right Join
491+
Output: t1."C 1", t2.c1, t3.c1
492+
Merge Cond: (t3.c1 = t1."C 1")
493+
-> Foreign Scan
494+
Output: t3.c1, t2.c1
495+
Relations: (public.ft2 t3) LEFT JOIN (public.ft1 t2)
496+
Remote SQL: SELECT r3."C 1", r2."C 1" FROM ("S 1"."T 1" r3 LEFT JOIN "S 1"."T 1" r2 ON (((r2."C 1" = r3."C 1")))) ORDER BY r3."C 1" ASC NULLS LAST
497+
-> Index Only Scan using t1_pkey on "S 1"."T 1" t1
498+
Output: t1."C 1"
499+
(11 rows)
500+
501+
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 left join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
502+
C 1 | c1 | c1
503+
-----+-----+-----
504+
101 | 101 | 101
505+
102 | 102 | 102
506+
103 | 103 | 103
507+
104 | 104 | 104
508+
105 | 105 | 105
509+
106 | 106 | 106
510+
107 | 107 | 107
511+
108 | 108 | 108
512+
109 | 109 | 109
513+
110 | 110 | 110
514+
(10 rows)
515+
516+
-- Test similar to above with all full outer joins
517+
EXPLAIN (COSTS false, VERBOSE)
518+
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
519+
QUERY PLAN
520+
------------------------------------------------------------------------------------------------------------------------------------------------------------------
521+
Limit
522+
Output: t1."C 1", t2.c1, t3.c1
523+
-> Merge Full Join
524+
Output: t1."C 1", t2.c1, t3.c1
525+
Merge Cond: (t3.c1 = t1."C 1")
526+
-> Foreign Scan
527+
Output: t2.c1, t3.c1
528+
Relations: (public.ft1 t2) FULL JOIN (public.ft2 t3)
529+
Remote SQL: SELECT r2."C 1", r3."C 1" FROM ("S 1"."T 1" r2 FULL JOIN "S 1"."T 1" r3 ON (((r2."C 1" = r3."C 1")))) ORDER BY r3."C 1" ASC NULLS LAST
530+
-> Index Only Scan using t1_pkey on "S 1"."T 1" t1
531+
Output: t1."C 1"
532+
(11 rows)
533+
534+
SELECT t1."C 1", t2.c1, t3.c1 FROM "S 1"."T 1" t1 full join ft1 t2 full join ft2 t3 on (t2.c1 = t3.c1) on (t3.c1 = t1."C 1") OFFSET 100 LIMIT 10;
535+
C 1 | c1 | c1
536+
-----+-----+-----
537+
101 | 101 | 101
538+
102 | 102 | 102
539+
103 | 103 | 103
540+
104 | 104 | 104
541+
105 | 105 | 105
542+
106 | 106 | 106
543+
107 | 107 | 107
544+
108 | 108 | 108
545+
109 | 109 | 109
546+
110 | 110 | 110
547+
(10 rows)
548+
481549
RESET enable_hashjoin;
482550
RESET enable_nestloop;
483551
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 32 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -669,6 +669,15 @@ postgresGetForeignRelSize(PlannerInfo *root,
669669
* we have and want to test whether any of them are useful. For a foreign
670670
* table, we don't know what indexes are present on the remote side but
671671
* want to speculate about which ones we'd like to use if they existed.
672+
*
673+
* This function returns a list of potentially-useful equivalence classes,
674+
* but it does not guarantee that an EquivalenceMember exists which contains
675+
* Vars only from the given relation. For example, given ft1 JOIN t1 ON
676+
* ft1.x + t1.x = 0, this function will say that the equivalence class
677+
* containing ft1.x + t1.x is potentially useful. Supposing ft1 is remote and
678+
* t1 is local (or on a different server), it will turn out that no useful
679+
* ORDER BY clause can be generated. It's not our job to figure that out
680+
* here; we're only interested in identifying relevant ECs.
672681
*/
673682
staticList*
674683
get_useful_ecs_for_relation(PlannerInfo*root,RelOptInfo*rel)
@@ -721,13 +730,32 @@ get_useful_ecs_for_relation(PlannerInfo *root, RelOptInfo *rel)
721730
/*
722731
* restrictinfo->mergeopfamilies != NIL is sufficient to guarantee
723732
* that left_ec and right_ec will be initialized, per comments in
724-
* distribute_qual_to_rels, and rel->joininfo should only contain ECs
725-
* where this relation appears on one side or the other.
733+
* distribute_qual_to_rels.
734+
*
735+
* We want to identify which side of this merge-joinable clause
736+
* contains columns from the relation produced by this RelOptInfo. We
737+
* test for overlap, not containment, because there could be extra
738+
* relations on either side. For example, suppose we've got something
739+
* like ((A JOIN B ON A.x = B.x) JOIN C ON A.y = C.y) LEFT JOIN D ON
740+
* A.y = D.y. The input rel might be the joinrel between A and B, and
741+
* we'll consider the join clause A.y = D.y. relids contains a
742+
* relation not involved in the join class (B) and the equivalence
743+
* class for the left-hand side of the clause contains a relation not
744+
* involved in the input rel (C). Despite the fact that we have only
745+
* overlap and not containment in either direction, A.y is potentially
746+
* useful as a sort column.
747+
*
748+
* Note that it's even possible that relids overlaps neither side of
749+
* the join clause. For example, consider A LEFT JOIN B ON A.x = B.x
750+
* AND A.x = 1. The clause A.x = 1 will appear in B's joininfo list,
751+
* but overlaps neither side of B. In that case, we just skip this
752+
* join clause, since it doesn't suggest a useful sort order for this
753+
* relation.
726754
*/
727-
if (bms_is_subset(relids,restrictinfo->right_ec->ec_relids))
755+
if (bms_overlap(relids,restrictinfo->right_ec->ec_relids))
728756
useful_eclass_list=list_append_unique_ptr(useful_eclass_list,
729757
restrictinfo->right_ec);
730-
elseif (bms_is_subset(relids,restrictinfo->left_ec->ec_relids))
758+
elseif (bms_overlap(relids,restrictinfo->left_ec->ec_relids))
731759
useful_eclass_list=list_append_unique_ptr(useful_eclass_list,
732760
restrictinfo->left_ec);
733761
}

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -242,6 +242,16 @@ SELECT t1.c1, t2."C 1" FROM ft2 t1 LEFT JOIN "S 1"."T 1" t2 ON (t1.c1 = t2."C 1"
242242
EXPLAIN (COSTS false, VERBOSE)
243243
SELECT t1."C 1"FROM"S 1"."T 1" t1left join ft1 t2join ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
244244
SELECT t1."C 1"FROM"S 1"."T 1" t1left join ft1 t2join ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
245+
-- Test similar to above, except that the full join prevents any equivalence
246+
-- classes from being merged. This produces single relation equivalence classes
247+
-- included in join restrictions.
248+
EXPLAIN (COSTS false, VERBOSE)
249+
SELECT t1."C 1",t2.c1,t3.c1FROM"S 1"."T 1" t1left join ft1 t2 fulljoin ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
250+
SELECT t1."C 1",t2.c1,t3.c1FROM"S 1"."T 1" t1left join ft1 t2 fulljoin ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
251+
-- Test similar to above with all full outer joins
252+
EXPLAIN (COSTS false, VERBOSE)
253+
SELECT t1."C 1",t2.c1,t3.c1FROM"S 1"."T 1" t1 fulljoin ft1 t2 fulljoin ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
254+
SELECT t1."C 1",t2.c1,t3.c1FROM"S 1"."T 1" t1 fulljoin ft1 t2 fulljoin ft2 t3on (t2.c1=t3.c1)on (t3.c1= t1."C 1") OFFSET100LIMIT10;
245255
RESET enable_hashjoin;
246256
RESET enable_nestloop;
247257

0 commit comments

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