public inbox for [email protected]
help / color / mirror / Atom feedFrom: Baji Shaik <[email protected]>
To: Michael Paquier <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: [PATCH] Add regression tests for btree skip scan support functions
Date: Fri, 5 Jun 2026 13:45:49 -0500
Message-ID: <CA+fm-RNPKqYC1Jbvpn1xh3vSwx0KJNh3LzfYjnoRoTJhyZw28w@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+fm-RNqXnr_ms365xQnSMcggrGFozbEPrVa+Y_yGYrSky1o2w@mail.gmail.com>
<[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)
view thread (3+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Add regression tests for btree skip scan support functions
In-Reply-To: <CA+fm-RNPKqYC1Jbvpn1xh3vSwx0KJNh3LzfYjnoRoTJhyZw28w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox