src/test/regress/expected/tidrangescan.out - master - githubmirror/postgres

src/test/regress/expected/tidrangescan.out
405 строк10 KB
David Rowley
Add parallelism support for TID Range Scans
27 ноя 2025, 04:05
27 ноя 2025, 04:050ca3b16
-- tests for tidrangescans
SET enable_seqscan TO off;
CREATE TABLE tidrangescan(id integer, data text);
-- empty table
EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid < '(1, 0)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid < '(1,0)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid < '(1, 0)';
 ctid 
------
(0 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid > '(9, 0)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid > '(9,0)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid > '(9, 0)';
 ctid 
------
(0 rows)

-- insert enough tuples to fill at least two pages
INSERT INTO tidrangescan SELECT i,repeat('x', 100) FROM generate_series(1,200) AS s(i);
-- remove all tuples after the 10th tuple on each page.  Trying to ensure
-- we get the same layout with all CPU architectures and smaller than standard
-- page sizes.
DELETE FROM tidrangescan
WHERE substring(ctid::text FROM ',(\d+)\)')::integer > 10 OR substring(ctid::text FROM '\((\d+),')::integer > 2;
VACUUM tidrangescan;
-- range scans with upper bound
EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid < '(1,0)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid < '(1,0)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid < '(1,0)';
  ctid  
--------
 (0,1)
 (0,2)
 (0,3)
 (0,4)
 (0,5)
 (0,6)
 (0,7)
 (0,8)
 (0,9)
 (0,10)
(10 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid <= '(1,5)';
             QUERY PLAN             
------------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid <= '(1,5)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid <= '(1,5)';
  ctid  
--------
 (0,1)
 (0,2)
 (0,3)
 (0,4)
 (0,5)
 (0,6)
 (0,7)
 (0,8)
 (0,9)
 (0,10)
 (1,1)
 (1,2)
 (1,3)
 (1,4)
 (1,5)
(15 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid < '(0,0)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid < '(0,0)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid < '(0,0)';
 ctid 
------
(0 rows)

-- range scans with lower bound
EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid > '(2,8)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid > '(2,8)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid > '(2,8)';
  ctid  
--------
 (2,9)
 (2,10)
(2 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE '(2,8)' < ctid;
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: ('(2,8)'::tid < ctid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE '(2,8)' < ctid;
  ctid  
--------
 (2,9)
 (2,10)
(2 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid >= '(2,8)';
             QUERY PLAN             
------------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid >= '(2,8)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid >= '(2,8)';
  ctid  
--------
 (2,8)
 (2,9)
 (2,10)
(3 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid >= '(100,0)';
              QUERY PLAN              
--------------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid >= '(100,0)'::tid)
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid >= '(100,0)';
 ctid 
------
(0 rows)

-- range scans with both bounds
EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE ctid > '(1,4)' AND '(1,7)' >= ctid;
                           QUERY PLAN                           
----------------------------------------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: ((ctid > '(1,4)'::tid) AND ('(1,7)'::tid >= ctid))
(2 rows)

SELECT ctid FROM tidrangescan WHERE ctid > '(1,4)' AND '(1,7)' >= ctid;
 ctid  
-------
 (1,5)
 (1,6)
 (1,7)
(3 rows)

EXPLAIN (COSTS OFF)
SELECT ctid FROM tidrangescan WHERE '(1,7)' >= ctid AND ctid > '(1,4)';
                           QUERY PLAN                           
----------------------------------------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (('(1,7)'::tid >= ctid) AND (ctid > '(1,4)'::tid))
(2 rows)

SELECT ctid FROM tidrangescan WHERE '(1,7)' >= ctid AND ctid > '(1,4)';
 ctid  
-------
 (1,5)
 (1,6)
 (1,7)
(3 rows)

-- extreme offsets
SELECT ctid FROM tidrangescan WHERE ctid > '(0,65535)' AND ctid < '(1,0)' LIMIT 1;
 ctid 
------
(0 rows)

SELECT ctid FROM tidrangescan WHERE ctid < '(0,0)' LIMIT 1;
 ctid 
------
(0 rows)

SELECT ctid FROM tidrangescan WHERE ctid > '(4294967295,65535)';
 ctid 
------
(0 rows)

SELECT ctid FROM tidrangescan WHERE ctid < '(0,0)';
 ctid 
------
(0 rows)

-- NULLs in the range cannot return tuples
SELECT ctid FROM tidrangescan WHERE ctid >= (SELECT NULL::tid);
 ctid 
------
(0 rows)

-- rescans
EXPLAIN (COSTS OFF)
SELECT t.ctid,t2.c FROM tidrangescan t,
LATERAL (SELECT count(*) c FROM tidrangescan t2 WHERE t2.ctid <= t.ctid) t2
WHERE t.ctid < '(1,0)';
                  QUERY PLAN                   
-----------------------------------------------
 Nested Loop
   ->  Tid Range Scan on tidrangescan t
         TID Cond: (ctid < '(1,0)'::tid)
   ->  Aggregate
         ->  Tid Range Scan on tidrangescan t2
               TID Cond: (ctid <= t.ctid)
(6 rows)

SELECT t.ctid,t2.c FROM tidrangescan t,
LATERAL (SELECT count(*) c FROM tidrangescan t2 WHERE t2.ctid <= t.ctid) t2
WHERE t.ctid < '(1,0)';
  ctid  | c  
--------+----
 (0,1)  |  1
 (0,2)  |  2
 (0,3)  |  3
 (0,4)  |  4
 (0,5)  |  5
 (0,6)  |  6
 (0,7)  |  7
 (0,8)  |  8
 (0,9)  |  9
 (0,10) | 10
(10 rows)

-- cursors
-- Ensure we get a TID Range scan without a Materialize node.
EXPLAIN (COSTS OFF)
DECLARE c SCROLL CURSOR FOR SELECT ctid FROM tidrangescan WHERE ctid < '(1,0)';
            QUERY PLAN             
-----------------------------------
 Tid Range Scan on tidrangescan
   TID Cond: (ctid < '(1,0)'::tid)
(2 rows)

BEGIN;
DECLARE c SCROLL CURSOR FOR SELECT ctid FROM tidrangescan WHERE ctid < '(1,0)';
FETCH NEXT c;
 ctid  
-------
 (0,1)
(1 row)

FETCH NEXT c;
 ctid  
-------
 (0,2)
(1 row)

FETCH PRIOR c;
 ctid  
-------
 (0,1)
(1 row)

FETCH FIRST c;
 ctid  
-------
 (0,1)
(1 row)

FETCH LAST c;
  ctid  
--------
 (0,10)
(1 row)

COMMIT;
DROP TABLE tidrangescan;
-- Tests for parallel TID Range Scans
BEGIN;
SET LOCAL parallel_setup_cost TO 0;
SET LOCAL parallel_tuple_cost TO 0;
SET LOCAL min_parallel_table_scan_size TO 0;
SET LOCAL max_parallel_workers_per_gather TO 4;
CREATE TABLE parallel_tidrangescan (id integer, data text)
WITH (fillfactor = 10);
-- Insert enough tuples such that each page gets 5 tuples with fillfactor = 10
INSERT INTO parallel_tidrangescan
SELECT i, repeat('x', 100) FROM generate_series(1,200) AS s(i);
-- Ensure there are 40 pages for parallel test
SELECT min(ctid), max(ctid) FROM parallel_tidrangescan;
  min  |  max   
-------+--------
 (0,1) | (39,5)
(1 row)

-- Parallel range scans with upper bound
EXPLAIN (COSTS OFF)
SELECT count(*) FROM parallel_tidrangescan WHERE ctid < '(30,1)';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Tid Range Scan on parallel_tidrangescan
                     TID Cond: (ctid < '(30,1)'::tid)
(6 rows)

SELECT count(*) FROM parallel_tidrangescan WHERE ctid < '(30,1)';
 count 
-------
   150
(1 row)

-- Parallel range scans with lower bound
EXPLAIN (COSTS OFF)
SELECT count(*) FROM parallel_tidrangescan WHERE ctid > '(10,0)';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Tid Range Scan on parallel_tidrangescan
                     TID Cond: (ctid > '(10,0)'::tid)
(6 rows)

SELECT count(*) FROM parallel_tidrangescan WHERE ctid > '(10,0)';
 count 
-------
   150
(1 row)

-- Parallel range scans with both bounds
EXPLAIN (COSTS OFF)
SELECT count(*) FROM parallel_tidrangescan WHERE ctid > '(10,0)' AND ctid < '(30,1)';
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 4
         ->  Partial Aggregate
               ->  Parallel Tid Range Scan on parallel_tidrangescan
                     TID Cond: ((ctid > '(10,0)'::tid) AND (ctid < '(30,1)'::tid))
(6 rows)

SELECT count(*) FROM parallel_tidrangescan WHERE ctid > '(10,0)' AND ctid < '(30,1)';
 count 
-------
   100
(1 row)

-- Parallel rescans
EXPLAIN (COSTS OFF)
SELECT t.ctid,t2.c FROM parallel_tidrangescan t,
LATERAL (SELECT count(*) c FROM parallel_tidrangescan t2 WHERE t2.ctid <= t.ctid) t2
WHERE t.ctid < '(1,0)';
                           QUERY PLAN                           
----------------------------------------------------------------
 Nested Loop
   ->  Gather
         Workers Planned: 4
         ->  Parallel Tid Range Scan on parallel_tidrangescan t
               TID Cond: (ctid < '(1,0)'::tid)
   ->  Aggregate
         ->  Tid Range Scan on parallel_tidrangescan t2
               TID Cond: (ctid <= t.ctid)
(8 rows)

SELECT t.ctid,t2.c FROM parallel_tidrangescan t,
LATERAL (SELECT count(*) c FROM parallel_tidrangescan t2 WHERE t2.ctid <= t.ctid) t2
WHERE t.ctid < '(1,0)';
 ctid  | c 
-------+---
 (0,1) | 1
 (0,2) | 2
 (0,3) | 3
 (0,4) | 4
 (0,5) | 5
(5 rows)

ROLLBACK;
RESET enable_seqscan;