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

Commitb9c755a

Browse files
committed
In clause_is_computable_at(), test required_relids for clone clauses.
Use the clause's required_relids not clause_relids for testingwhether it is computable at the current join level, if it is aclone clause generated by deconstruct_distribute_oj_quals().Arguably, this is more correct and we should do it for all clauses;that would at least remove the handwavy claim that we are doingit to save cycles compared to inspecting Vars individually.However, attempting to do that exposes that we are not being carefulto compute an accurate value for required_relids in all cases.I'm unsure whether it's a good idea to attempt to do that for v16,or leave it as future clean-up. In the meantime, this quick hackdemonstrably fixes some cases, so let's squeeze it in for beta1.Patch by me, but great thanks to Richard Guo for investigationand testing. The new test cases are all modeled on his examples.Discussion:https://postgr.es/m/CAMbWs4-_vwkBij4XOQ5ukxUvLgwTm0kS5_DO9CicUeKbEfKjUw@mail.gmail.com
1 parenteabb225 commitb9c755a

File tree

3 files changed

+100
-1
lines changed

3 files changed

+100
-1
lines changed

‎src/backend/optimizer/util/restrictinfo.c

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -544,13 +544,24 @@ clause_is_computable_at(PlannerInfo *root,
544544
RestrictInfo*rinfo,
545545
Relidseval_relids)
546546
{
547-
Relidsclause_relids=rinfo->clause_relids;
547+
Relidsclause_relids;
548548
ListCell*lc;
549549

550550
/* Nothing to do if no outer joins have been performed yet. */
551551
if (!bms_overlap(eval_relids,root->outer_join_rels))
552552
return true;
553553

554+
/*
555+
* For an ordinary qual clause, we consider the actual clause_relids as
556+
* explained above. However, it's possible for multiple members of a
557+
* group of clone quals to have the same clause_relids, so for clones use
558+
* the required_relids instead to ensure we select just one of them.
559+
*/
560+
if (rinfo->has_clone||rinfo->is_clone)
561+
clause_relids=rinfo->required_relids;
562+
else
563+
clause_relids=rinfo->clause_relids;
564+
554565
foreach(lc,root->join_info_list)
555566
{
556567
SpecialJoinInfo*sjinfo= (SpecialJoinInfo*)lfirst(lc);

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

Lines changed: 68 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2500,6 +2500,74 @@ select * from int4_tbl t1
25002500
-> Seq Scan on int4_tbl t4
25012501
(12 rows)
25022502

2503+
explain (costs off)
2504+
select * from int4_tbl t1
2505+
left join (int4_tbl t2 left join int4_tbl t3 on t2.f1 > 0) on t2.f1 > 1
2506+
left join int4_tbl t4 on t2.f1 > 2 and t3.f1 > 3
2507+
where t1.f1 = coalesce(t2.f1, 1);
2508+
QUERY PLAN
2509+
----------------------------------------------------
2510+
Nested Loop Left Join
2511+
Join Filter: ((t2.f1 > 2) AND (t3.f1 > 3))
2512+
-> Nested Loop Left Join
2513+
Join Filter: (t2.f1 > 0)
2514+
-> Nested Loop Left Join
2515+
Filter: (t1.f1 = COALESCE(t2.f1, 1))
2516+
-> Seq Scan on int4_tbl t1
2517+
-> Materialize
2518+
-> Seq Scan on int4_tbl t2
2519+
Filter: (f1 > 1)
2520+
-> Seq Scan on int4_tbl t3
2521+
-> Materialize
2522+
-> Seq Scan on int4_tbl t4
2523+
(13 rows)
2524+
2525+
explain (costs off)
2526+
select * from int4_tbl t1
2527+
left join ((select t2.f1 from int4_tbl t2
2528+
left join int4_tbl t3 on t2.f1 > 0
2529+
where t3.f1 is null) s
2530+
left join tenk1 t4 on s.f1 > 1)
2531+
on s.f1 = t1.f1;
2532+
QUERY PLAN
2533+
-------------------------------------------------
2534+
Nested Loop Left Join
2535+
Join Filter: (t2.f1 > 1)
2536+
-> Hash Right Join
2537+
Hash Cond: (t2.f1 = t1.f1)
2538+
-> Nested Loop Left Join
2539+
Join Filter: (t2.f1 > 0)
2540+
Filter: (t3.f1 IS NULL)
2541+
-> Seq Scan on int4_tbl t2
2542+
-> Materialize
2543+
-> Seq Scan on int4_tbl t3
2544+
-> Hash
2545+
-> Seq Scan on int4_tbl t1
2546+
-> Seq Scan on tenk1 t4
2547+
(13 rows)
2548+
2549+
explain (costs off)
2550+
select * from onek t1
2551+
left join onek t2 on t1.unique1 = t2.unique1
2552+
left join onek t3 on t2.unique1 = t3.unique1
2553+
left join onek t4 on t3.unique1 = t4.unique1 and t2.unique2 = t4.unique2;
2554+
QUERY PLAN
2555+
------------------------------------------------------------------------
2556+
Hash Left Join
2557+
Hash Cond: ((t3.unique1 = t4.unique1) AND (t2.unique2 = t4.unique2))
2558+
-> Hash Left Join
2559+
Hash Cond: (t2.unique1 = t3.unique1)
2560+
-> Hash Left Join
2561+
Hash Cond: (t1.unique1 = t2.unique1)
2562+
-> Seq Scan on onek t1
2563+
-> Hash
2564+
-> Seq Scan on onek t2
2565+
-> Hash
2566+
-> Seq Scan on onek t3
2567+
-> Hash
2568+
-> Seq Scan on onek t4
2569+
(13 rows)
2570+
25032571
--
25042572
-- check a case where we formerly got confused by conflicting sort orders
25052573
-- in redundant merge join path keys

‎src/test/regress/sql/join.sql

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -488,6 +488,26 @@ select * from int4_tbl t1
488488
left join int4_tbl t3ont2.f1=t3.f1
489489
left join int4_tbl t4ont3.f1!=t4.f1;
490490

491+
explain (costs off)
492+
select*from int4_tbl t1
493+
left join (int4_tbl t2left join int4_tbl t3ont2.f1>0)ont2.f1>1
494+
left join int4_tbl t4ont2.f1>2andt3.f1>3
495+
wheret1.f1= coalesce(t2.f1,1);
496+
497+
explain (costs off)
498+
select*from int4_tbl t1
499+
left join ((selectt2.f1from int4_tbl t2
500+
left join int4_tbl t3ont2.f1>0
501+
wheret3.f1 isnull) s
502+
left join tenk1 t4ons.f1>1)
503+
ons.f1=t1.f1;
504+
505+
explain (costs off)
506+
select*from onek t1
507+
left join onek t2ont1.unique1=t2.unique1
508+
left join onek t3ont2.unique1=t3.unique1
509+
left join onek t4ont3.unique1=t4.unique1andt2.unique2=t4.unique2;
510+
491511
--
492512
-- check a case where we formerly got confused by conflicting sort orders
493513
-- in redundant merge join path keys

0 commit comments

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