0001-Avoid-VCI-sort-after-non-VCI-join-in-planner.patch
text/x-patch
Filename: 0001-Avoid-VCI-sort-after-non-VCI-join-in-planner.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0001
Subject: Avoid VCI sort after non-VCI join in planner
| File | + | − |
|---|---|---|
| contrib/vci/expected/bugs.out | 38 | 0 |
| contrib/vci/sql/bugs.sql | 20 | 0 |
| contrib/vci/vci_read_guc.c | 2 | 2 |
From f7ed5462623f9a931a2d7f9cde6ee60ffcc1f5c3 Mon Sep 17 00:00:00 2001 From: Timur Magomedov <t.magomedov@postgrespro.ru> Date: Fri, 19 Sep 2025 16:26:40 +0300 Subject: [PATCH] Avoid VCI sort after non-VCI join in planner --- contrib/vci/expected/bugs.out | 38 +++++++++++++++++++++++++++++++++++ contrib/vci/sql/bugs.sql | 20 ++++++++++++++++++ contrib/vci/vci_read_guc.c | 4 ++-- 3 files changed, 60 insertions(+), 2 deletions(-) diff --git a/contrib/vci/expected/bugs.out b/contrib/vci/expected/bugs.out index d7e08dc6f7a..5872cfe1e02 100644 --- a/contrib/vci/expected/bugs.out +++ b/contrib/vci/expected/bugs.out @@ -94,3 +94,41 @@ EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) SELEC (4 rows) DROP TABLE t6; +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); +EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) +SELECT * + FROM main m + JOIN secondary s + ON m.id = s.main_id + WHERE s.val in ( + SELECT MAX(val) + FROM secondary s2 + WHERE s2.main_id = m.id) + ORDER BY s.val; + QUERY PLAN +-------------------------------------------------------------------------------------------------- + Sort (actual rows=0.00 loops=1) + Sort Key: s.val + Sort Method: quicksort Memory: 25kB + -> Nested Loop (actual rows=0.00 loops=1) + Join Filter: (s.val = (max(s2.val))) + -> Hash Join (actual rows=0.00 loops=1) + Hash Cond: (s.main_id = m.id) + -> Custom Scan (VCI Scan) using sec_vci on secondary s (actual rows=0.00 loops=1) + Disabled: true + -> Hash (never executed) + -> Index Only Scan using main_pkey on main m (never executed) + Heap Fetches: 0 + Index Searches: 0 + -> Custom Scan (VCI Aggregate) (never executed) + Disabled: true + -> Custom Scan (VCI Scan) using sec_vci on secondary s2 (never executed) + Disabled: true + Filter: (main_id = m.id) +(18 rows) + +DROP TABLE secondary; +DROP TABLE main; diff --git a/contrib/vci/sql/bugs.sql b/contrib/vci/sql/bugs.sql index 3b6b7303acb..49597abcf99 100644 --- a/contrib/vci/sql/bugs.sql +++ b/contrib/vci/sql/bugs.sql @@ -57,3 +57,23 @@ INSERT INTO t6 SELECT id, 'info' || id FROM generate_series(1, 500) id; ANALYZE t6; EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) SELECT max(id) FROM t6; DROP TABLE t6; + +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); + +EXPLAIN (ANALYZE, COSTS FALSE, BUFFERS FALSE, TIMING FALSE, SUMMARY FALSE) +SELECT * + FROM main m + JOIN secondary s + ON m.id = s.main_id + WHERE s.val in ( + SELECT MAX(val) + FROM secondary s2 + WHERE s2.main_id = m.id) + ORDER BY s.val; + +DROP TABLE secondary; +DROP TABLE main; diff --git a/contrib/vci/vci_read_guc.c b/contrib/vci/vci_read_guc.c index 3583944390f..6b95108824d 100644 --- a/contrib/vci/vci_read_guc.c +++ b/contrib/vci/vci_read_guc.c @@ -150,7 +150,7 @@ static struct config_bool VciConfigureNamesBool[] = NULL, }, &VciGuc.enable_hashjoin, - true, + false, NULL, NULL, NULL, }, @@ -162,7 +162,7 @@ static struct config_bool VciConfigureNamesBool[] = NULL, }, &VciGuc.enable_nestloop, - true, + false, NULL, NULL, NULL, }, -- 2.43.0