diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/tidscan.out | 46 | ||||
| -rw-r--r-- | src/test/regress/sql/tidscan.sql | 14 |
2 files changed, 60 insertions, 0 deletions
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out index 8f15c04dad1..9b5eb04bfd9 100644 --- a/src/test/regress/expected/tidscan.out +++ b/src/test/regress/expected/tidscan.out @@ -109,6 +109,7 @@ WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); (2 rows) -- nestloop-with-inner-tidscan joins on tid +SET enable_hashjoin TO off; -- otherwise hash join might win EXPLAIN (COSTS OFF) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; @@ -147,6 +148,7 @@ FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; (0,1) | 1 | (0,1) | 1 (1 row) +RESET enable_hashjoin; -- exercise backward scan and rewind BEGIN; DECLARE c CURSOR FOR @@ -231,4 +233,48 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ERROR: cursor "c" is not positioned on a row ROLLBACK; +-- bulk joins on CTID +-- (these plans don't use TID scans, but this still seems like an +-- appropriate place for these tests) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; + QUERY PLAN +---------------------------------------- + Aggregate + -> Hash Join + Hash Cond: (t1.ctid = t2.ctid) + -> Seq Scan on tenk1 t1 + -> Hash + -> Seq Scan on tenk1 t2 +(6 rows) + +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; + count +------- + 10000 +(1 row) + +SET enable_hashjoin TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; + QUERY PLAN +----------------------------------------- + Aggregate + -> Merge Join + Merge Cond: (t1.ctid = t2.ctid) + -> Sort + Sort Key: t1.ctid + -> Seq Scan on tenk1 t1 + -> Sort + Sort Key: t2.ctid + -> Seq Scan on tenk1 t2 +(9 rows) + +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; + count +------- + 10000 +(1 row) + +RESET enable_hashjoin; DROP TABLE tidscan; diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql index 2d63aa067fe..ef05c098420 100644 --- a/src/test/regress/sql/tidscan.sql +++ b/src/test/regress/sql/tidscan.sql @@ -40,6 +40,7 @@ SELECT ctid, * FROM tidscan WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1); -- nestloop-with-inner-tidscan joins on tid +SET enable_hashjoin TO off; -- otherwise hash join might win EXPLAIN (COSTS OFF) SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; @@ -50,6 +51,7 @@ SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; SELECT t1.ctid, t1.*, t2.ctid, t2.* FROM tidscan t1 LEFT JOIN tidscan t2 ON t1.ctid = t2.ctid WHERE t1.id = 1; +RESET enable_hashjoin; -- exercise backward scan and rewind BEGIN; @@ -80,4 +82,16 @@ EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF) UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *; ROLLBACK; +-- bulk joins on CTID +-- (these plans don't use TID scans, but this still seems like an +-- appropriate place for these tests) +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; +SET enable_hashjoin TO off; +EXPLAIN (COSTS OFF) +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; +SELECT count(*) FROM tenk1 t1 JOIN tenk1 t2 ON t1.ctid = t2.ctid; +RESET enable_hashjoin; + DROP TABLE tidscan; |