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
Appearance settings

Commit c2df2ed

Browse filesBrowse files
tglsfdctglsfdc
committed
Try to stabilize flappy test result.
This recently-added test case checks the plan of an inner join between two identical tables. It's just chance which join order the planner will pick, and in the presence of any variation in the underlying statistics, the displayed plan might change. Add a WHERE condition to break the cost symmetry and hopefully stabilize matters. (We're still trying to understand exactly why the underlying statistics aren't as stable as intended, but this seems like a good change anyway, since this test would surely bite us again in future.) While here, clean up assorted comment spelling, grammar, and whitespace problems. Discussion: https://postgr.es/m/4168116.1711720146@sss.pgh.pa.us
1 parent d3ae2a2 commit c2df2ed
Copy full SHA for c2df2ed

File tree

Expand file treeCollapse file tree

2 files changed

+15
-12
lines changed
Filter options
Expand file treeCollapse file tree

2 files changed

+15
-12
lines changed

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/subselect.out
+8-6Lines 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-up the direct-correlated ANY_SUBLINK
1980+
-- Pull up direct-correlated ANY_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 not avaiable.
2012+
-- but a.hundred is not available.
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 not avaiable for this.
2029+
-- but a.odd is not available 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 references is available
2045+
-- should be able to pull up since all the references are 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/subselect.sql
+7-6Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -983,7 +983,7 @@ explain (verbose, costs off)
983983
with x as (select * from subselect_tbl)
984984
select * from x for update;
985985

986-
-- Pull-up the direct-correlated ANY_SUBLINK
986+
-- Pull up direct-correlated ANY_SUBLINKs
987987
explain (costs off)
988988
select * from tenk1 A where hundred in (select hundred from tenk2 B where B.odd = A.odd);
989989

@@ -994,28 +994,29 @@ where A.hundred in (select C.hundred FROM tenk2 C
994994
WHERE c.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 not avaiable.
997+
-- but a.hundred is not available.
998998
explain (costs off)
999999
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
10001000
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.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 not avaiable for this.
1003+
-- but a.odd is not available for this.
10041004
explain (costs off)
10051005
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
10061006
ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = a.odd);
10071007

1008-
-- should be able to pull up since all the references is available
1008+
-- should be able to pull up since all the references are available.
10091009
explain (costs off)
10101010
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
10111011
ON B.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
10121012

10131013
-- we can pull up the sublink into the inner JoinExpr.
10141014
explain (costs off)
10151015
SELECT * FROM tenk1 A INNER JOIN tenk2 B
1016-
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd);
1016+
ON A.hundred in (SELECT c.hundred FROM tenk2 C WHERE c.odd = b.odd)
1017+
WHERE a.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 A LEFT JOIN tenk2 B
1021-
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
1022+
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.