From 09f34919697ab550ad6305566dbff396db4747e4 Mon Sep 17 00:00:00 2001 From: Timur Magomedov Date: Wed, 24 Sep 2025 16:29:55 +0300 Subject: [PATCH v25 2/2] Check VCI Sort node runs --- contrib/vci/expected/bugs.out | 14 ++++++++++++++ contrib/vci/sql/bugs.sql | 6 ++++++ 2 files changed, 20 insertions(+) diff --git a/contrib/vci/expected/bugs.out b/contrib/vci/expected/bugs.out index 5872cfe1e02..70e0534dd7c 100644 --- a/contrib/vci/expected/bugs.out +++ b/contrib/vci/expected/bugs.out @@ -98,6 +98,8 @@ CREATE TABLE main (id BIGSERIAL PRIMARY KEY); CREATE TABLE secondary (id BIGSERIAL PRIMARY KEY, main_id BIGINT REFERENCES main (id), val INTEGER); CREATE INDEX main_vci ON main USING vci (id); CREATE INDEX sec_vci ON secondary USING vci (id, main_id, val); +-- Check VCI Sort is not put on top of non-VCI join +-- See https://www.postgresql.org/message-id/a27f68845af78d404459fcab940bfae2ec7755e5.camel%40postgrespro.ru EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) SELECT * FROM main m @@ -130,5 +132,17 @@ SELECT * Filter: (main_id = m.id) (18 rows) +-- Check VCI Sort is used if suitable +EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) +SELECT * FROM secondary s ORDER BY s.val; + QUERY PLAN +-------------------------------------------------------------------------------------- + Custom Scan (VCI Sort) (actual rows=0.00 loops=1) + Sort Key: val + Sort Method: quicksort Memory: 25kB + -> Custom Scan (VCI Scan) using sec_vci on secondary s (actual rows=0.00 loops=1) + Disabled: true +(5 rows) + DROP TABLE secondary; DROP TABLE main; diff --git a/contrib/vci/sql/bugs.sql b/contrib/vci/sql/bugs.sql index 49597abcf99..d4affcd479f 100644 --- a/contrib/vci/sql/bugs.sql +++ b/contrib/vci/sql/bugs.sql @@ -64,6 +64,8 @@ CREATE TABLE secondary (id BIGSERIAL PRIMARY KEY, main_id BIGINT REFERENCES main CREATE INDEX main_vci ON main USING vci (id); CREATE INDEX sec_vci ON secondary USING vci (id, main_id, val); +-- Check VCI Sort is not put on top of non-VCI join +-- See https://www.postgresql.org/message-id/a27f68845af78d404459fcab940bfae2ec7755e5.camel%40postgrespro.ru EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) SELECT * FROM main m @@ -75,5 +77,9 @@ SELECT * WHERE s2.main_id = m.id) ORDER BY s.val; +-- Check VCI Sort is used if suitable +EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) +SELECT * FROM secondary s ORDER BY s.val; + DROP TABLE secondary; DROP TABLE main; -- 2.43.0