public inbox for [email protected]help / color / mirror / Atom feed
[PATCH] Add regression tests for btree skip scan support functions 3+ messages / 2 participants [nested] [flat]
* [PATCH] Add regression tests for btree skip scan support functions @ 2026-06-04 00:16 Baji Shaik <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Baji Shaik @ 2026-06-04 00:16 UTC (permalink / raw) To: [email protected]; +Cc: [email protected]; [email protected] Hi, While looking at coverage for nbtcompare.c on coverage.postgresql.org, I noticed several btree skip-scan support routines (the per-type skipsupport / increment / decrement helpers) are not exercised by the existing regression tests. Specifically: oid8: btoid8skipsupport, oid8_decrement, oid8_increment int8: btint8skipsupport, int8_decrement, int8_increment bool: btboolskipsupport, bool_decrement, bool_increment char: btcharskipsupport, char_decrement, char_increment oid: oid_decrement int2: int2_decrement These functions feed into the skip-scan optimization added in commit 92fe23d93aa for PG18, but the existing regression coverage only exercises a few types (mostly int4 and varchar) on the leading column of multi-column indexes. The attached patch adds a small section to btree_index.sql that creates a two-column index (a, b) for each missing type, then runs forward and backward Index Only Scans with a predicate on the non-leading column (b). This forces the planner into a skip scan, which in turn calls the per-type skip-support function plus the increment/decrement helpers as it generates skip-array elements. No code change in this patch. It's regression coverage only. Tested with make check (245/245 pass). Thanks, Baji Shaik Attachments: [application/octet-stream] 0001-Add-regression-tests-for-btree-skip-scan-support-fun.patch (10.9K, 3-0001-Add-regression-tests-for-btree-skip-scan-support-fun.patch) download | inline diff: From e8c1f8d2a4b8e7c5d6e3f4a5b6c7d8e9f0a1b2c3 Mon Sep 17 00:00:00 2001 From: Baji Shaik <[email protected]> Date: Wed, 27 May 2026 07:11:27 -0500 Subject: [PATCH] Add regression tests for btree skip scan support functions Several btree skip scan support functions in nbtcompare.c have zero test coverage as shown by coverage.postgresql.org: oid8: btoid8skipsupport, oid8_decrement, oid8_increment int8: btint8skipsupport, int8_decrement, int8_increment bool: btboolskipsupport, bool_decrement, bool_increment char: btcharskipsupport, char_decrement, char_increment oid: oid_decrement int2: int2_decrement Add tests that exercise skip scans (both forward and backward) on multi-column indexes with these types as the leading column. This triggers the per-type skip-support function and the increment/ decrement helpers via the Index Only Scan path. Author: Baji Shaik <[email protected]> Reviewed-by: Discussion: --- diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 21dc9b5783a..111f5d4b682 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -736,3 +736,235 @@ ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); ERROR: ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_part_idx" DETAIL: This operation is not supported for partitioned indexes. DROP TABLE btree_part; +-- +-- Test btree skip scan support for types that currently lack test coverage +-- (exercises btXXXskipsupport, XXX_decrement, XXX_increment functions) +-- +SET enable_seqscan = off; +SET enable_bitmapscan = off; +-- int8: btint8skipsupport, int8_decrement, int8_increment +CREATE TABLE skip_int8 (a int8, b int4); +INSERT INTO skip_int8 SELECT i / 10, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_int8_a_b_idx on skip_int8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; + a | b +---+--- + 0 | 5 + 1 | 5 + 2 | 5 + 3 | 5 + 4 | 5 + 5 | 5 + 6 | 5 + 7 | 5 + 8 | 5 + 9 | 5 +(10 rows) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_int8_a_b_idx on skip_int8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_int8; +-- oid8: btoid8skipsupport, oid8_decrement, oid8_increment +CREATE TABLE skip_oid8 (a oid8, b int4); +INSERT INTO skip_oid8 SELECT (i / 10)::int8::oid8, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_oid8_a_b_idx on skip_oid8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; + a | b +---+--- + 0 | 5 + 1 | 5 + 2 | 5 + 3 | 5 + 4 | 5 + 5 | 5 + 6 | 5 + 7 | 5 + 8 | 5 + 9 | 5 +(10 rows) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_oid8_a_b_idx on skip_oid8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_oid8; +-- bool: btboolskipsupport, bool_decrement, bool_increment +CREATE TABLE skip_bool (a bool, b int4); +INSERT INTO skip_bool SELECT (i % 2 = 0), i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_bool (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_bool_a_b_idx on skip_bool + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; + a | b +---+---- + t | 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_bool_a_b_idx on skip_bool + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; + a | b +---+---- + t | 50 +(1 row) + +DROP TABLE skip_bool; +-- "char": btcharskipsupport, char_decrement, char_increment +CREATE TABLE skip_char (a "char", b int4); +INSERT INTO skip_char SELECT chr(ascii('a') + (i % 26))::"char", i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_char (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_char_a_b_idx on skip_char + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; + a | b +---+---- + y | 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_char_a_b_idx on skip_char + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; + a | b +---+---- + y | 50 +(1 row) + +DROP TABLE skip_char; +-- oid: oid_decrement (btoidskipsupport and oid_increment already covered) +CREATE TABLE skip_oid (a oid, b int4); +INSERT INTO skip_oid SELECT (i / 10)::oid, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +------------------------------------------------------------- + Index Only Scan Backward using skip_oid_a_b_idx on skip_oid + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_oid; +-- int2: int2_decrement (btint2skipsupport and int2_increment already covered) +CREATE TABLE skip_int2 (a int2, b int4); +INSERT INTO skip_int2 SELECT (i / 10)::int2, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int2 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_int2_a_b_idx on skip_int2 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_int2; +RESET enable_seqscan; +RESET enable_bitmapscan; diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql index 6aaaa386abc..1109f1e0506 100644 --- a/src/test/regress/sql/btree_index.sql +++ b/src/test/regress/sql/btree_index.sql @@ -438,3 +438,79 @@ CREATE TABLE btree_part (id int4) PARTITION BY RANGE (id); CREATE INDEX btree_part_idx ON btree_part(id); ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); DROP TABLE btree_part; + +-- +-- Test btree skip scan support for types that currently lack test coverage +-- (exercises btXXXskipsupport, XXX_decrement, XXX_increment functions) +-- +SET enable_seqscan = off; +SET enable_bitmapscan = off; + +-- int8: btint8skipsupport, int8_decrement, int8_increment +CREATE TABLE skip_int8 (a int8, b int4); +INSERT INTO skip_int8 SELECT i / 10, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_int8; + +-- oid8: btoid8skipsupport, oid8_decrement, oid8_increment +CREATE TABLE skip_oid8 (a oid8, b int4); +INSERT INTO skip_oid8 SELECT (i / 10)::int8::oid8, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_oid8; + +-- bool: btboolskipsupport, bool_decrement, bool_increment +CREATE TABLE skip_bool (a bool, b int4); +INSERT INTO skip_bool SELECT (i % 2 = 0), i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_bool (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; +DROP TABLE skip_bool; + +-- "char": btcharskipsupport, char_decrement, char_increment +CREATE TABLE skip_char (a "char", b int4); +INSERT INTO skip_char SELECT chr(ascii('a') + (i % 26))::"char", i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_char (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; +DROP TABLE skip_char; + +-- oid: oid_decrement (btoidskipsupport and oid_increment already covered) +CREATE TABLE skip_oid (a oid, b int4); +INSERT INTO skip_oid SELECT (i / 10)::oid, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_oid; + +-- int2: int2_decrement (btint2skipsupport and int2_increment already covered) +CREATE TABLE skip_int2 (a int2, b int4); +INSERT INTO skip_int2 SELECT (i / 10)::int2, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int2 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_int2; + +RESET enable_seqscan; +RESET enable_bitmapscan; -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCH] Add regression tests for btree skip scan support functions @ 2026-06-05 03:32 Michael Paquier <[email protected]> parent: Baji Shaik <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Michael Paquier @ 2026-06-05 03:32 UTC (permalink / raw) To: Baji Shaik <[email protected]>; +Cc: [email protected]; [email protected] On Wed, Jun 03, 2026 at 07:16:15PM -0500, Baji Shaik wrote: > These functions feed into the skip-scan optimization added in > commit 92fe23d93aa for PG18, but the existing regression coverage > only exercises a few types (mostly int4 and varchar) on the leading > column of multi-column indexes. Thanks a lot for this patch. FWIW, I've noticed the gap a couple of months ago when adding oid8 but I have not been able to get back to it, noticing that the gap was wider than only oid8. > The attached patch adds a small section to btree_index.sql that > creates a two-column index (a, b) for each missing type, then runs > forward and backward Index Only Scans with a predicate on the > non-leading column (b). This forces the planner into a skip scan, > which in turn calls the per-type skip-support function plus the > increment/decrement helpers as it generates skip-array elements. Could you add this patch to the next commit fest please at [1]? That's to make sure that we don't forget about it when v20 opens for business. I am sure that nobody would complain it this gets applied on HEAD today (argument that this is an open item for v19 due to v19?), but that's a coverage improvement so let's just do that once REL_19_STABLE is forked. Perhaps we could do something for the overflow cases in the increment functions of oid, oid8, char, int8 and int4. int2_increment is lacking entirely. For the decrement functions, we still lack something for the overflow of int2, int8, char. You can run a coverage report to check all that: https://www.postgresql.org/docs/devel/regress-coverage.html [1]: https://commitfest.postgresql.org/59/ -- Michael Attachments: [application/pgp-signature] signature.asc (833B, 2-signature.asc) download ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [PATCH] Add regression tests for btree skip scan support functions @ 2026-06-05 18:45 Baji Shaik <[email protected]> parent: Michael Paquier <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Baji Shaik @ 2026-06-05 18:45 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: [email protected]; [email protected] On Thu, Jun 4, 2026 at 10:32 PM Michael Paquier <[email protected]> wrote: > Could you add this patch to the next commit fest please at [1]? > That's to make sure that we don't forget about it when v20 opens for > business. I am sure that nobody would complain it this gets applied > on HEAD today (argument that this is an open item for v19 due to > v19?), but that's a coverage improvement so let's just do that once > REL_19_STABLE is forked. > > Perhaps we could do something for the overflow cases in the increment > functions of oid, oid8, char, int8 and int4. int2_increment is > lacking entirely. For the decrement functions, we still lack > something for the overflow of int2, int8, char. You can run a > coverage report to check all that: > https://www.postgresql.org/docs/devel/regress-coverage.htm > <https://www.postgresql.org/docs/devel/regress-coverage.html>l Thanks for the review. Added to commitfest 59 today [1] and posting v2 which addresses the coverage gaps. v2 adds: - basic int2_increment coverage (was lacking entirely as you noted) - boundary tables for int2/int4/int8/oid/oid8/"char" around MIN/MAX, with forward and backward Index Only Scans, exercising the *_increment overflow / *_decrement underflow comparisons Coverage on src/backend/access/nbtree/nbtcompare.c (--enable-coverage build, regression suite): master: 58.75 % lines, 63.10 % branches taken at least once v1: 89.06 % lines, 73.81 % branches taken at least once v2: 90.94 % lines, 82.14 % branches taken at least once A couple of *_increment overflow code blocks (the body of the "if (val == TYPE_MAX)" check) still read as unreached. The scan walks the rows at the boundary value and exits without probing past the end, so the increment function gets called with intermediate values rather than the boundary value itself. The boundary comparison is exercised either way, which is what the +8 pp branch delta over v1 reflects. If you or anyone else has ideas for forcing the few remaining branches I'm happy to extend. All 245 regression tests pass; all 129 isolation tests pass. Patch attached. [1]: https://commitfest.postgresql.org/patch/6852/ Thanks, Baji Shaik Attachments: [application/octet-stream] v2-0001-Add-regression-tests-for-btree-skip-scan-support-fun.patch (25.7K, 3-v2-0001-Add-regression-tests-for-btree-skip-scan-support-fun.patch) download | inline diff: From 4846fc3340fee19eb927e21b69d68074b65dacf2 Mon Sep 17 00:00:00 2001 From: Baji Shaik <[email protected]> Date: Fri, 5 Jun 2026 13:41:42 -0500 Subject: [PATCH] Add regression tests for btree skip scan support functions Several btree skip-scan support functions in nbtcompare.c lack regression coverage: btint8skipsupport, btoid8skipsupport, btboolskipsupport, btcharskipsupport, int2_increment, and the overflow/underflow branches of int2/int4/int8/oid/oid8/char increment/decrement helpers. Add coverage by: - exercising each per-type skipsupport plus the increment/decrement helpers via Index Only Scan with predicate on the non-leading column of a multi-column btree index; - building boundary tables (rows at INT*_MAX, INT*_MIN, OID_MAX, OID8_MAX, byte 0/255 for "char", etc.) and running forward/backward Index Only Scans, to exercise the *_increment overflow and *_decrement underflow paths. Coverage on src/backend/access/nbtree/nbtcompare.c improves as follows (regression tests, --enable-coverage build): master: 58.75 % lines, 63.10 % branches patched: 90.94 % lines, 82.14 % branches Some boundary branches remain unreached because skip-scan termination does not always probe past the range end; the patch still exercises the comparison evaluating to the boundary value, increasing branch coverage substantially. No code change in this patch -- regression coverage only. Author: Baji Shaik <[email protected]> --- src/test/regress/expected/btree_index.out | 502 ++++++++++++++++++++++ src/test/regress/sql/btree_index.sql | 182 ++++++++ 2 files changed, 684 insertions(+) diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index 21dc9b5783a..354d0d9bcd9 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -736,3 +736,505 @@ ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); ERROR: ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_part_idx" DETAIL: This operation is not supported for partitioned indexes. DROP TABLE btree_part; +-- +-- Test btree skip scan support for types that currently lack test coverage +-- (exercises btXXXskipsupport, XXX_decrement, XXX_increment functions) +-- +SET enable_seqscan = off; +SET enable_bitmapscan = off; +-- int8: btint8skipsupport, int8_decrement, int8_increment +CREATE TABLE skip_int8 (a int8, b int4); +INSERT INTO skip_int8 SELECT i / 10, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_int8_a_b_idx on skip_int8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; + a | b +---+--- + 0 | 5 + 1 | 5 + 2 | 5 + 3 | 5 + 4 | 5 + 5 | 5 + 6 | 5 + 7 | 5 + 8 | 5 + 9 | 5 +(10 rows) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_int8_a_b_idx on skip_int8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_int8; +-- oid8: btoid8skipsupport, oid8_decrement, oid8_increment +CREATE TABLE skip_oid8 (a oid8, b int4); +INSERT INTO skip_oid8 SELECT (i / 10)::int8::oid8, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_oid8_a_b_idx on skip_oid8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; + a | b +---+--- + 0 | 5 + 1 | 5 + 2 | 5 + 3 | 5 + 4 | 5 + 5 | 5 + 6 | 5 + 7 | 5 + 8 | 5 + 9 | 5 +(10 rows) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_oid8_a_b_idx on skip_oid8 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_oid8; +-- bool: btboolskipsupport, bool_decrement, bool_increment +CREATE TABLE skip_bool (a bool, b int4); +INSERT INTO skip_bool SELECT (i % 2 = 0), i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_bool (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_bool_a_b_idx on skip_bool + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; + a | b +---+---- + t | 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_bool_a_b_idx on skip_bool + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; + a | b +---+---- + t | 50 +(1 row) + +DROP TABLE skip_bool; +-- "char": btcharskipsupport, char_decrement, char_increment +CREATE TABLE skip_char (a "char", b int4); +INSERT INTO skip_char SELECT chr(ascii('a') + (i % 26))::"char", i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_char (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_char_a_b_idx on skip_char + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; + a | b +---+---- + y | 50 +(1 row) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_char_a_b_idx on skip_char + Index Cond: (b = 50) +(2 rows) + +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; + a | b +---+---- + y | 50 +(1 row) + +DROP TABLE skip_char; +-- oid: btoidskipsupport, oid_decrement, oid_increment +CREATE TABLE skip_oid (a oid, b int4); +INSERT INTO skip_oid SELECT (i / 10)::oid, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +------------------------------------------------------------- + Index Only Scan Backward using skip_oid_a_b_idx on skip_oid + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_oid; +-- int2: btint2skipsupport, int2_decrement, int2_increment +CREATE TABLE skip_int2 (a int2, b int4); +INSERT INTO skip_int2 SELECT (i / 10)::int2, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int2 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a; + QUERY PLAN +------------------------------------------------------ + Index Only Scan using skip_int2_a_b_idx on skip_int2 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a; + a | b +---+--- + 0 | 5 + 1 | 5 + 2 | 5 + 3 | 5 + 4 | 5 + 5 | 5 + 6 | 5 + 7 | 5 + 8 | 5 + 9 | 5 +(10 rows) + +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------- + Index Only Scan Backward using skip_int2_a_b_idx on skip_int2 + Index Cond: (b = 5) +(2 rows) + +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; + a | b +---+--- + 9 | 5 + 8 | 5 + 7 | 5 + 6 | 5 + 5 | 5 + 4 | 5 + 3 | 5 + 2 | 5 + 1 | 5 + 0 | 5 +(10 rows) + +DROP TABLE skip_int2; +-- +-- Boundary cases: exercise the *_increment overflow / *_decrement +-- underflow branches in skip-scan helpers. +-- +-- int2 boundaries (int2_increment overflow at INT2_MAX, int2_decrement +-- underflow at INT2_MIN) +CREATE TABLE skip_int2_max (a int2, b int4); +INSERT INTO skip_int2_max VALUES (32765::int2, 1), (32766::int2, 1), (32767::int2, 1); +CREATE INDEX ON skip_int2_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int2_max WHERE b = 1 ORDER BY a; + QUERY PLAN +-------------------------------------------------------------- + Index Only Scan using skip_int2_max_a_b_idx on skip_int2_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int2_max WHERE b = 1 ORDER BY a; + a | b +-------+--- + 32765 | 1 + 32766 | 1 + 32767 | 1 +(3 rows) + +DROP TABLE skip_int2_max; +CREATE TABLE skip_int2_min (a int2, b int4); +INSERT INTO skip_int2_min VALUES ((-32766)::int2, 1), ((-32767)::int2, 1), ((-32768)::int2, 1); +CREATE INDEX ON skip_int2_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int2_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +----------------------------------------------------------------------- + Index Only Scan Backward using skip_int2_min_a_b_idx on skip_int2_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int2_min WHERE b = 1 ORDER BY a DESC; + a | b +--------+--- + -32766 | 1 + -32767 | 1 + -32768 | 1 +(3 rows) + +DROP TABLE skip_int2_min; +-- int4 boundaries +CREATE TABLE skip_int4_max (a int4, b int4); +INSERT INTO skip_int4_max VALUES (2147483645, 1), (2147483646, 1), (2147483647, 1); +CREATE INDEX ON skip_int4_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int4_max WHERE b = 1 ORDER BY a; + QUERY PLAN +-------------------------------------------------------------- + Index Only Scan using skip_int4_max_a_b_idx on skip_int4_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int4_max WHERE b = 1 ORDER BY a; + a | b +------------+--- + 2147483645 | 1 + 2147483646 | 1 + 2147483647 | 1 +(3 rows) + +DROP TABLE skip_int4_max; +CREATE TABLE skip_int4_min (a int4, b int4); +INSERT INTO skip_int4_min VALUES (-2147483646, 1), (-2147483647, 1), (-2147483648, 1); +CREATE INDEX ON skip_int4_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int4_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +----------------------------------------------------------------------- + Index Only Scan Backward using skip_int4_min_a_b_idx on skip_int4_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int4_min WHERE b = 1 ORDER BY a DESC; + a | b +-------------+--- + -2147483646 | 1 + -2147483647 | 1 + -2147483648 | 1 +(3 rows) + +DROP TABLE skip_int4_min; +-- int8 boundaries +CREATE TABLE skip_int8_max (a int8, b int4); +INSERT INTO skip_int8_max VALUES (9223372036854775805::int8, 1), + (9223372036854775806::int8, 1), + (9223372036854775807::int8, 1); +CREATE INDEX ON skip_int8_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int8_max WHERE b = 1 ORDER BY a; + QUERY PLAN +-------------------------------------------------------------- + Index Only Scan using skip_int8_max_a_b_idx on skip_int8_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int8_max WHERE b = 1 ORDER BY a; + a | b +---------------------+--- + 9223372036854775805 | 1 + 9223372036854775806 | 1 + 9223372036854775807 | 1 +(3 rows) + +DROP TABLE skip_int8_max; +CREATE TABLE skip_int8_min (a int8, b int4); +INSERT INTO skip_int8_min VALUES ((-9223372036854775806)::int8, 1), + ((-9223372036854775807)::int8, 1), + ((-9223372036854775808)::int8, 1); +CREATE INDEX ON skip_int8_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int8_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +----------------------------------------------------------------------- + Index Only Scan Backward using skip_int8_min_a_b_idx on skip_int8_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_int8_min WHERE b = 1 ORDER BY a DESC; + a | b +----------------------+--- + -9223372036854775806 | 1 + -9223372036854775807 | 1 + -9223372036854775808 | 1 +(3 rows) + +DROP TABLE skip_int8_min; +-- oid boundaries (oid_increment overflow at OID_MAX, oid_decrement underflow at 0) +CREATE TABLE skip_oid_max (a oid, b int4); +INSERT INTO skip_oid_max VALUES (4294967293::oid, 1), (4294967294::oid, 1), (4294967295::oid, 1); +CREATE INDEX ON skip_oid_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid_max WHERE b = 1 ORDER BY a; + QUERY PLAN +------------------------------------------------------------ + Index Only Scan using skip_oid_max_a_b_idx on skip_oid_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_oid_max WHERE b = 1 ORDER BY a; + a | b +------------+--- + 4294967293 | 1 + 4294967294 | 1 + 4294967295 | 1 +(3 rows) + +DROP TABLE skip_oid_max; +CREATE TABLE skip_oid_min (a oid, b int4); +INSERT INTO skip_oid_min VALUES (0::oid, 1), (1::oid, 1), (2::oid, 1); +CREATE INDEX ON skip_oid_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +--------------------------------------------------------------------- + Index Only Scan Backward using skip_oid_min_a_b_idx on skip_oid_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_oid_min WHERE b = 1 ORDER BY a DESC; + a | b +---+--- + 2 | 1 + 1 | 1 + 0 | 1 +(3 rows) + +DROP TABLE skip_oid_min; +-- oid8 boundaries (uint64 range) +CREATE TABLE skip_oid8_max (a oid8, b int4); +INSERT INTO skip_oid8_max VALUES ('18446744073709551613'::oid8, 1), + ('18446744073709551614'::oid8, 1), + ('18446744073709551615'::oid8, 1); +CREATE INDEX ON skip_oid8_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid8_max WHERE b = 1 ORDER BY a; + QUERY PLAN +-------------------------------------------------------------- + Index Only Scan using skip_oid8_max_a_b_idx on skip_oid8_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_oid8_max WHERE b = 1 ORDER BY a; + a | b +----------------------+--- + 18446744073709551613 | 1 + 18446744073709551614 | 1 + 18446744073709551615 | 1 +(3 rows) + +DROP TABLE skip_oid8_max; +CREATE TABLE skip_oid8_min (a oid8, b int4); +INSERT INTO skip_oid8_min VALUES (0::oid8, 1), (1::oid8, 1), (2::oid8, 1); +CREATE INDEX ON skip_oid8_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid8_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +----------------------------------------------------------------------- + Index Only Scan Backward using skip_oid8_min_a_b_idx on skip_oid8_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_oid8_min WHERE b = 1 ORDER BY a DESC; + a | b +---+--- + 2 | 1 + 1 | 1 + 0 | 1 +(3 rows) + +DROP TABLE skip_oid8_min; +-- "char" boundaries (uint8 byte range: char_increment overflow at 255, +-- char_decrement underflow at 0). "char" input cast accepts -128..127 for +-- a signed int1, so bytes 254 and 255 are written as -2 and -1. +CREATE TABLE skip_char_max (a "char", b int4); +INSERT INTO skip_char_max VALUES ((-3)::"char", 1), ((-2)::"char", 1), ((-1)::"char", 1); +CREATE INDEX ON skip_char_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_char_max WHERE b = 1 ORDER BY a; + QUERY PLAN +-------------------------------------------------------------- + Index Only Scan using skip_char_max_a_b_idx on skip_char_max + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_char_max WHERE b = 1 ORDER BY a; + a | b +------+--- + \375 | 1 + \376 | 1 + \377 | 1 +(3 rows) + +DROP TABLE skip_char_max; +CREATE TABLE skip_char_min (a "char", b int4); +INSERT INTO skip_char_min VALUES (0::"char", 1), (1::"char", 1), (2::"char", 1); +CREATE INDEX ON skip_char_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_char_min WHERE b = 1 ORDER BY a DESC; + QUERY PLAN +----------------------------------------------------------------------- + Index Only Scan Backward using skip_char_min_a_b_idx on skip_char_min + Index Cond: (b = 1) +(2 rows) + +SELECT a, b FROM skip_char_min WHERE b = 1 ORDER BY a DESC; + a | b +------+--- + \x02 | 1 + \x01 | 1 + | 1 +(3 rows) + +DROP TABLE skip_char_min; +RESET enable_seqscan; +RESET enable_bitmapscan; diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql index 6aaaa386abc..95d30a919f2 100644 --- a/src/test/regress/sql/btree_index.sql +++ b/src/test/regress/sql/btree_index.sql @@ -438,3 +438,185 @@ CREATE TABLE btree_part (id int4) PARTITION BY RANGE (id); CREATE INDEX btree_part_idx ON btree_part(id); ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); DROP TABLE btree_part; + +-- +-- Test btree skip scan support for types that currently lack test coverage +-- (exercises btXXXskipsupport, XXX_decrement, XXX_increment functions) +-- +SET enable_seqscan = off; +SET enable_bitmapscan = off; + +-- int8: btint8skipsupport, int8_decrement, int8_increment +CREATE TABLE skip_int8 (a int8, b int4); +INSERT INTO skip_int8 SELECT i / 10, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_int8 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_int8; + +-- oid8: btoid8skipsupport, oid8_decrement, oid8_increment +CREATE TABLE skip_oid8 (a oid8, b int4); +INSERT INTO skip_oid8 SELECT (i / 10)::int8::oid8, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid8 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_oid8 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_oid8; + +-- bool: btboolskipsupport, bool_decrement, bool_increment +CREATE TABLE skip_bool (a bool, b int4); +INSERT INTO skip_bool SELECT (i % 2 = 0), i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_bool (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; +SELECT a, b FROM skip_bool WHERE b = 50 ORDER BY a DESC; +DROP TABLE skip_bool; + +-- "char": btcharskipsupport, char_decrement, char_increment +CREATE TABLE skip_char (a "char", b int4); +INSERT INTO skip_char SELECT chr(ascii('a') + (i % 26))::"char", i FROM generate_series(1, 100) i; +CREATE INDEX ON skip_char (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; +SELECT a, b FROM skip_char WHERE b = 50 ORDER BY a DESC; +DROP TABLE skip_char; + +-- oid: btoidskipsupport, oid_decrement, oid_increment +CREATE TABLE skip_oid (a oid, b int4); +INSERT INTO skip_oid SELECT (i / 10)::oid, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_oid (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_oid WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_oid; + +-- int2: btint2skipsupport, int2_decrement, int2_increment +CREATE TABLE skip_int2 (a int2, b int4); +INSERT INTO skip_int2 SELECT (i / 10)::int2, i % 10 FROM generate_series(1, 100) i; +CREATE INDEX ON skip_int2 (a, b); +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a; +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a; +EXPLAIN (COSTS OFF) +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; +SELECT a, b FROM skip_int2 WHERE b = 5 ORDER BY a DESC; +DROP TABLE skip_int2; + +-- +-- Boundary cases: exercise the *_increment overflow / *_decrement +-- underflow branches in skip-scan helpers. +-- +-- int2 boundaries (int2_increment overflow at INT2_MAX, int2_decrement +-- underflow at INT2_MIN) +CREATE TABLE skip_int2_max (a int2, b int4); +INSERT INTO skip_int2_max VALUES (32765::int2, 1), (32766::int2, 1), (32767::int2, 1); +CREATE INDEX ON skip_int2_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int2_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_int2_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_int2_max; + +CREATE TABLE skip_int2_min (a int2, b int4); +INSERT INTO skip_int2_min VALUES ((-32766)::int2, 1), ((-32767)::int2, 1), ((-32768)::int2, 1); +CREATE INDEX ON skip_int2_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int2_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_int2_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_int2_min; + +-- int4 boundaries +CREATE TABLE skip_int4_max (a int4, b int4); +INSERT INTO skip_int4_max VALUES (2147483645, 1), (2147483646, 1), (2147483647, 1); +CREATE INDEX ON skip_int4_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int4_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_int4_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_int4_max; + +CREATE TABLE skip_int4_min (a int4, b int4); +INSERT INTO skip_int4_min VALUES (-2147483646, 1), (-2147483647, 1), (-2147483648, 1); +CREATE INDEX ON skip_int4_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int4_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_int4_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_int4_min; + +-- int8 boundaries +CREATE TABLE skip_int8_max (a int8, b int4); +INSERT INTO skip_int8_max VALUES (9223372036854775805::int8, 1), + (9223372036854775806::int8, 1), + (9223372036854775807::int8, 1); +CREATE INDEX ON skip_int8_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int8_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_int8_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_int8_max; + +CREATE TABLE skip_int8_min (a int8, b int4); +INSERT INTO skip_int8_min VALUES ((-9223372036854775806)::int8, 1), + ((-9223372036854775807)::int8, 1), + ((-9223372036854775808)::int8, 1); +CREATE INDEX ON skip_int8_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_int8_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_int8_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_int8_min; + +-- oid boundaries (oid_increment overflow at OID_MAX, oid_decrement underflow at 0) +CREATE TABLE skip_oid_max (a oid, b int4); +INSERT INTO skip_oid_max VALUES (4294967293::oid, 1), (4294967294::oid, 1), (4294967295::oid, 1); +CREATE INDEX ON skip_oid_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_oid_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_oid_max; + +CREATE TABLE skip_oid_min (a oid, b int4); +INSERT INTO skip_oid_min VALUES (0::oid, 1), (1::oid, 1), (2::oid, 1); +CREATE INDEX ON skip_oid_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_oid_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_oid_min; + +-- oid8 boundaries (uint64 range) +CREATE TABLE skip_oid8_max (a oid8, b int4); +INSERT INTO skip_oid8_max VALUES ('18446744073709551613'::oid8, 1), + ('18446744073709551614'::oid8, 1), + ('18446744073709551615'::oid8, 1); +CREATE INDEX ON skip_oid8_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid8_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_oid8_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_oid8_max; + +CREATE TABLE skip_oid8_min (a oid8, b int4); +INSERT INTO skip_oid8_min VALUES (0::oid8, 1), (1::oid8, 1), (2::oid8, 1); +CREATE INDEX ON skip_oid8_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_oid8_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_oid8_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_oid8_min; + +-- "char" boundaries (uint8 byte range: char_increment overflow at 255, +-- char_decrement underflow at 0). "char" input cast accepts -128..127 for +-- a signed int1, so bytes 254 and 255 are written as -2 and -1. +CREATE TABLE skip_char_max (a "char", b int4); +INSERT INTO skip_char_max VALUES ((-3)::"char", 1), ((-2)::"char", 1), ((-1)::"char", 1); +CREATE INDEX ON skip_char_max (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_char_max WHERE b = 1 ORDER BY a; +SELECT a, b FROM skip_char_max WHERE b = 1 ORDER BY a; +DROP TABLE skip_char_max; + +CREATE TABLE skip_char_min (a "char", b int4); +INSERT INTO skip_char_min VALUES (0::"char", 1), (1::"char", 1), (2::"char", 1); +CREATE INDEX ON skip_char_min (a, b); +EXPLAIN (COSTS OFF) SELECT a, b FROM skip_char_min WHERE b = 1 ORDER BY a DESC; +SELECT a, b FROM skip_char_min WHERE b = 1 ORDER BY a DESC; +DROP TABLE skip_char_min; + +RESET enable_seqscan; +RESET enable_bitmapscan; -- 2.50.1 (Apple Git-155) ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-06-05 18:45 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-06-04 00:16 [PATCH] Add regression tests for btree skip scan support functions Baji Shaik <[email protected]> 2026-06-05 03:32 ` Michael Paquier <[email protected]> 2026-06-05 18:45 ` Baji Shaik <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox