Index-only scan for btree_gist turns bpchar to char
Alexander Law <exclusion@gmail.com>
From: Alexander Lakhin <exclusion@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2022-01-04T14:00:00Z
Lists: pgsql-hackers
Hello hackers,
While testing the index-only scan fix, I've discovered that replacing
the index-only scan with the index scan changes contrib/btree_gist
output because index-only scan for btree_gist returns a string without
padding.
A simple demonstration (based on btree_gist/sql/char.sql):
CREATE EXTENSION btree_gist;
CREATE TABLE chartmp (a char(32));
INSERT INTO chartmp VALUES('31b0');
CREATE INDEX charidx ON chartmp USING gist ( a );
SET enable_seqscan=off;
EXPLAIN VERBOSE SELECT *, octet_length(a) FROM chartmp WHERE a BETWEEN
'31a' AND '31c';
SELECT *, octet_length(a) FROM chartmp WHERE a BETWEEN '31a' AND '31c';
SET enable_indexonlyscan=off;
EXPLAIN VERBOSE SELECT *, octet_length(a) FROM chartmp WHERE a BETWEEN
'31a' AND '31c';
SELECT *, octet_length(a) FROM chartmp WHERE a BETWEEN '31a' AND '31c';
QUERY
PLAN
------------------------------------------------------------------------------
Index Only Scan using charidx on chartmp (cost=0.12..8.15 rows=1
width=136)
Index Cond: ((a >= '31a'::bpchar) AND (a <= '31c'::bpchar))
(2 rows)
a | octet_length
------+--------------
31b0 | 4
(1 row)
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using charidx on chartmp (cost=0.12..8.15 rows=1 width=136)
Index Cond: ((a >= '31a'::bpchar) AND (a <= '31c'::bpchar))
(2 rows)
a | octet_length
----------------------------------+--------------
31b0 | 32
(1 row)
It seems that loosing blank padding is incorrect (btree and btree_gin
preserve padding with index-only scan) but it's recorded in
contrib/btree_gist/expected/char.out.
Best regards,
Alexander
Commits
-
Fix results of index-only scans on btree_gist char(N) indexes.
- b2198690847f 10.20 landed
- 823d4c7e2538 13.6 landed
- 54b1cb7eb784 15.0 landed
- 3a69d45afcc8 12.10 landed
- 38cbdd22d633 11.15 landed
- 043c1e1a73aa 14.2 landed