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

Commitc2df2ed

Browse files
committed
Try to stabilize flappy test result.
This recently-added test case checks the plan of an inner joinbetween two identical tables. It's just chance which join orderthe planner will pick, and in the presence of any variation inthe underlying statistics, the displayed plan might change.Add a WHERE condition to break the cost symmetry and hopefullystabilize matters.(We're still trying to understand exactly why the underlyingstatistics aren't as stable as intended, but this seems likea good change anyway, since this test would surely bite usagain in future.)While here, clean up assorted comment spelling, grammar, andwhitespace problems.Discussion:https://postgr.es/m/4168116.1711720146@sss.pgh.pa.us
1 parentd3ae2a2 commitc2df2ed

File tree

2 files changed

+15
-12
lines changed

2 files changed

+15
-12
lines changed

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

Lines changed: 8 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1977,7 +1977,7 @@ select * from x for update;
19771977
Output: subselect_tbl.f1, subselect_tbl.f2, subselect_tbl.f3
19781978
(2 rows)
19791979

1980-
-- Pull-upthedirect-correlatedANY_SUBLINK
1980+
-- Pullup direct-correlatedANY_SUBLINKs
19811981
explain (costs off)
19821982
select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd);
19831983
QUERY PLAN
@@ -2009,7 +2009,7 @@ WHERE c.odd = b.odd));
20092009
(8 rows)
20102010

20112011
-- we should only try to pull up the sublink into RHS of a left join
2012-
-- but a.hundred is notavaiable.
2012+
-- but a.hundred is notavailable.
20132013
explain (costs off)
20142014
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
20152015
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
@@ -2026,7 +2026,7 @@ ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
20262026
(8 rows)
20272027

20282028
-- we should only try to pull up the sublink into RHS of a left join
2029-
-- but a.odd is notavaiable for this.
2029+
-- but a.odd is notavailable for this.
20302030
explain (costs off)
20312031
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
20322032
ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
@@ -2042,7 +2042,7 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
20422042
Filter: (odd = a.odd)
20432043
(8 rows)
20442044

2045-
-- should be able to pull up since all the referencesis available
2045+
-- should be able to pull up since all the referencesare available.
20462046
explain (costs off)
20472047
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
20482048
ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
@@ -2063,21 +2063,23 @@ ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
20632063
-- we can pull up the sublink into the inner JoinExpr.
20642064
explain (costs off)
20652065
SELECT * FROM tenk1 A INNER JOIN tenk2 B
2066-
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
2066+
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd)
2067+
WHERE a.thousand < 750;
20672068
QUERY PLAN
20682069
-------------------------------------------------
20692070
Hash Join
20702071
Hash Cond: (c.odd = b.odd)
20712072
-> Hash Join
20722073
Hash Cond: (a.hundred = c.hundred)
20732074
-> Seq Scan on tenk1 a
2075+
Filter: (thousand < 750)
20742076
-> Hash
20752077
-> HashAggregate
20762078
Group Key: c.odd, c.hundred
20772079
-> Seq Scan on tenk2 c
20782080
-> Hash
20792081
-> Seq Scan on tenk2 b
2080-
(11 rows)
2082+
(12 rows)
20812083

20822084
-- we can pull up the aggregate sublink into RHS of a left join.
20832085
explain (costs off)

‎src/test/regress/sql/subselect.sql

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -983,7 +983,7 @@ explain (verbose, costs off)
983983
with xas (select*from subselect_tbl)
984984
select*from x forupdate;
985985

986-
-- Pull-upthedirect-correlatedANY_SUBLINK
986+
-- Pullup direct-correlatedANY_SUBLINKs
987987
explain (costs off)
988988
select*from tenk1 Awhere hundredin (select hundredfrom tenk2 BwhereB.odd=A.odd);
989989

@@ -994,28 +994,29 @@ where A.hundred in (select C.hundred FROM tenk2 C
994994
WHEREc.odd=b.odd));
995995

996996
-- we should only try to pull up the sublink into RHS of a left join
997-
-- but a.hundred is notavaiable.
997+
-- but a.hundred is notavailable.
998998
explain (costs off)
999999
SELECT*FROM tenk1 ALEFT JOIN tenk2 B
10001000
ONA.hundredin (SELECTc.hundredFROM tenk2 CWHEREc.odd=b.odd);
10011001

10021002
-- we should only try to pull up the sublink into RHS of a left join
1003-
-- but a.odd is notavaiable for this.
1003+
-- but a.odd is notavailable for this.
10041004
explain (costs off)
10051005
SELECT*FROM tenk1 ALEFT JOIN tenk2 B
10061006
ONB.hundredin (SELECTc.hundredFROM tenk2 CWHEREc.odd=a.odd);
10071007

1008-
-- should be able to pull up since all the referencesis available
1008+
-- should be able to pull up since all the referencesare available.
10091009
explain (costs off)
10101010
SELECT*FROM tenk1 ALEFT JOIN tenk2 B
10111011
ONB.hundredin (SELECTc.hundredFROM tenk2 CWHEREc.odd=b.odd);
10121012

10131013
-- we can pull up the sublink into the inner JoinExpr.
10141014
explain (costs off)
10151015
SELECT*FROM tenk1 AINNER JOIN tenk2 B
1016-
ONA.hundredin (SELECTc.hundredFROM tenk2 CWHEREc.odd=b.odd);
1016+
ONA.hundredin (SELECTc.hundredFROM tenk2 CWHEREc.odd=b.odd)
1017+
WHEREa.thousand<750;
10171018

10181019
-- we can pull up the aggregate sublink into RHS of a left join.
10191020
explain (costs off)
10201021
SELECT*FROM tenk1 ALEFT JOIN tenk2 B
1021-
ONB.hundredin (SELECTmin(c.hundred)FROM tenk2 CWHEREc.odd=b.odd);
1022+
ONB.hundredin (SELECTmin(c.hundred)FROM tenk2 CWHEREc.odd=b.odd);

0 commit comments

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