BUG #17350: GIST TRGM Index is broken when combining with combining INCLUDE with a string function (e.g. lower).
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: louis.jachiet@telecom-paris.fr
Date: 2021-12-30T15:47:06Z
Lists: pgsql-bugs
The following bug has been logged on the website:
Bug reference: 17350
Logged by: louis jachiet
Email address: louis.jachiet@telecom-paris.fr
PostgreSQL version: 13.4
Operating system: Archlinux
Description:
Hello everyone,
I hope this is not a duplicate of a similar bug (I tried to check…). GIST
TRGM Index seems to return
wrong values when populated with a string function (such as lower) and an
include. If I issue the
following lines, instead of returning 'foo' and 'bar', postgresql will
return two empty lines:
CREATE EXTENSION IF NOT EXISTS pg_trgm ;
CREATE TABLE t (a VARCHAR(50));
INSERT INTO t VALUES ('foo') ;
INSERT INTO t VALUES ('BAR') ;
CREATE INDEX test_idx ON t USING gist(lower(a) gist_trgm_ops) INCLUDE
(a) ;
set enable_seqscan=off ;
SELECT lower(a) FROM t ;
Obviously for this last SELECT, the index is only useful because of the
seqscan=off
but if you have a large database and issue a command like:
SELECT 1 FROM t WHERE lower(a) LIKE '%o%' ;
then the output will be also empty because the index will return empty lines
and the recheck condition will fail.
If I use a function different than lower (e.g. upper) the problem persists
but if remove the function or if remove the
include then everything works just fine. It really seems that it is the
combination of include + string function + gist trgm
that makes the SELECT return empty lines.
Thank you for your time!
Regards
Commits
-
Fix index-only scan plans, take 2.
- ec367452179c 11.15 landed
- d228af79d0f2 14.2 landed
- 9c4f38908447 12.10 landed
- 9a3ddeb519e8 15.0 landed
- 7d344f00413a 10.20 landed
- 20d08b2c61cd 13.6 landed
-
Fix index-only scan plans when not all index columns can be returned.
- f789b7732e0b 12.10 landed
- e3a4c7981667 11.15 landed
- cabea571d11e 14.2 landed
- 70a31a0e3445 10.20 landed
- 4ace45677652 15.0 landed
- 45ae4271410b 13.6 landed