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

Commit7122f9d

Browse files
committed
Fix bit-rotted planner test case.
While fooling with my pet outer-join-variables patch, I discoveredthat the test case I added in commit11086f2 no longer demonstrateswhat it's supposed to. The idea is to tempt the planner to reversethe order of the two outer joins, which would leave noplace tocorrectly evaluate the WHERE clause that's inserted between them.Before the addition of the delay_upper_joins mechanism, it wouldhave taken the bait.However, subsequent improvements broke the test in two different ways.First, we now recognize the IS NULL coding pattern as an antijoin, andwe won't re-order antijoins; even if we did, the IS NULL test clausesget removed so there would be no opportunity for them to misbehave.Second, the planner now discovers that nested parameterized indexscansare a lot cheaper than the double hash join it used back in the day,and that approach doesn't want to re-order the joins anyway. Thus,in HEAD the test passes even if one dikes out delay_upper_joins.To fix, change the IS NULL tests to COALESCE clauses, which producethe same results but the planner isn't smart enough to convert themto antijoins. It'll still go for parameterized indexscans though,so drop the index enabling that (don't know why I added that in thefirst place), and disable nestloop joining just to be sure.This time around, add an EXPLAIN to make the choice of plan visible.
1 parent0efecb5 commit7122f9d

File tree

2 files changed

+50
-6
lines changed

2 files changed

+50
-6
lines changed

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

Lines changed: 32 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2630,26 +2630,55 @@ reset enable_memoize;
26302630
--
26312631
create temp table tt3(f1 int, f2 text);
26322632
insert into tt3 select x, repeat('xyzzy', 100) from generate_series(1,10000) x;
2633-
create index tt3i on tt3(f1);
26342633
analyze tt3;
26352634
create temp table tt4(f1 int);
26362635
insert into tt4 values (0),(1),(9999);
26372636
analyze tt4;
2637+
set enable_nestloop to off;
2638+
EXPLAIN (COSTS OFF)
2639+
SELECT a.f1
2640+
FROM tt4 a
2641+
LEFT JOIN (
2642+
SELECT b.f1
2643+
FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1)
2644+
WHERE COALESCE(c.f1, 0) = 0
2645+
) AS d ON (a.f1 = d.f1)
2646+
WHERE COALESCE(d.f1, 0) = 0
2647+
ORDER BY 1;
2648+
QUERY PLAN
2649+
-----------------------------------------------
2650+
Sort
2651+
Sort Key: a.f1
2652+
-> Hash Right Join
2653+
Hash Cond: (b.f1 = a.f1)
2654+
Filter: (COALESCE(b.f1, 0) = 0)
2655+
-> Hash Left Join
2656+
Hash Cond: (b.f1 = c.f1)
2657+
Filter: (COALESCE(c.f1, 0) = 0)
2658+
-> Seq Scan on tt3 b
2659+
-> Hash
2660+
-> Seq Scan on tt3 c
2661+
-> Hash
2662+
-> Seq Scan on tt4 a
2663+
(13 rows)
2664+
26382665
SELECT a.f1
26392666
FROM tt4 a
26402667
LEFT JOIN (
26412668
SELECT b.f1
26422669
FROM tt3 b LEFT JOIN tt3 c ON (b.f1 = c.f1)
2643-
WHERE c.f1 IS NULL
2670+
WHERECOALESCE(c.f1, 0) = 0
26442671
) AS d ON (a.f1 = d.f1)
2645-
WHERE d.f1 IS NULL;
2672+
WHERE COALESCE(d.f1, 0) = 0
2673+
ORDER BY 1;
26462674
f1
26472675
------
26482676
0
26492677
1
26502678
9999
26512679
(3 rows)
26522680

2681+
reset enable_nestloop;
26532682
--
26542683
-- basic semijoin and antijoin recognition tests
26552684
--

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

Lines changed: 18 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -608,21 +608,36 @@ reset enable_memoize;
608608

609609
create temp table tt3(f1int, f2text);
610610
insert into tt3select x, repeat('xyzzy',100)from generate_series(1,10000) x;
611-
createindextt3ion tt3(f1);
612611
analyze tt3;
613612

614613
create temp table tt4(f1int);
615614
insert into tt4values (0),(1),(9999);
616615
analyze tt4;
617616

617+
set enable_nestloop to off;
618+
619+
EXPLAIN (COSTS OFF)
620+
SELECTa.f1
621+
FROM tt4 a
622+
LEFT JOIN (
623+
SELECTb.f1
624+
FROM tt3 bLEFT JOIN tt3 cON (b.f1=c.f1)
625+
WHERE COALESCE(c.f1,0)=0
626+
)AS dON (a.f1=d.f1)
627+
WHERE COALESCE(d.f1,0)=0
628+
ORDER BY1;
629+
618630
SELECTa.f1
619631
FROM tt4 a
620632
LEFT JOIN (
621633
SELECTb.f1
622634
FROM tt3 bLEFT JOIN tt3 cON (b.f1=c.f1)
623-
WHEREc.f1 ISNULL
635+
WHERECOALESCE(c.f1,0)=0
624636
)AS dON (a.f1=d.f1)
625-
WHEREd.f1 ISNULL;
637+
WHERE COALESCE(d.f1,0)=0
638+
ORDER BY1;
639+
640+
reset enable_nestloop;
626641

627642
--
628643
-- basic semijoin and antijoin recognition tests

0 commit comments

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