From: | Alexander Lakhin <exclusion(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Index-only scan for btree_gist turns bpchar to char |
Date: | 2022-01-04 14:00:00 |
Message-ID: | 696c995b-b37f-5526-f45d-04abe713179f@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2022-01-04 14:01:31 | Re: SQL/JSON: functions |
Previous Message | Andrew Dunstan | 2022-01-04 13:53:13 | Re: SQL/JSON: functions |