Hi,
I was playing a bit with different types of indexes when I noticed that
I was getting an incorrect result for composite GIST indexes if the
first column of the index uses pg_trgm options and the execution plan is
an index only scan.
Here are the (verbose) steps to reproduce the problem in an empty
database:
Setup:
root=# SELECT version();
version
---------------------------------------------------------------
9.6.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18)
6.3.0 20170516, 64-bit
(1 fila)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE words ( id SERIAL PRIMARY KEY, w VARCHAR );
INSERT INTO words (w) VALUES ('Lorem'), ('ipsum');
Queries that make a seq scan yield correct results:
root=# SELECT w FROM words WHERE w LIKE '%e%';
w
-------
Lorem
(1 fila)
root=# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
QUERY PLAN
--------------------------------------------------------------
Seq Scan on words (cost=0.00..1.02 rows=2 width=6) (actual
time=0.018..0.020 rows=1 loops=1)
Filter: ((w)::text ~~ '%e%'::text)
Rows Removed by Filter: 1
Planning time: 0.112 ms
Execution time: 0.040 ms
(5 filas)
Index scan with simple index works fine also:
root=# SET enable_seqscan = OFF;
SET
root=# CREATE INDEX ON words USING GIST(w gist_trgm_ops);
CREATE INDEX
root=# SELECT w FROM words WHERE w LIKE '%e%';
w
-------
Lorem
(1 fila)
root=# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
QUERY
PLAN
------------------------------------------------------------------
Index Scan using words_w_idx on words (cost=0.13..8.16 rows=2
width=32) (actual time=0.053..0.054 rows=1 loops=1)
Index Cond: ((w)::text ~~ '%e%'::text)
Rows Removed by Index Recheck: 1
Planning time: 0.101 ms
Execution time: 0.114 ms
(5 filas)
Queries that use the index only scan return no results:
root=# CREATE INDEX ON words USING GIST(w gist_trgm_ops, w);
CREATE INDEX
root=# VACUUM ANALYZE words;
VACUUM
root=# SELECT w FROM words WHERE w LIKE '%e%';
w
---
(0 filas)
root=# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
QUERY
PLAN
-------------------------------------------------------------------- Index Only Scan using words_w_w1_idx on
words (cost=0.13..4.16 rows=2 width=6) (actual time=0.043..0.043 rows=0 loops=1)
Index Cond: (w ~~ '%e%'::text)
Rows Removed by Index Recheck: 2
Heap Fetches: 0
Planning time: 0.114 ms
Execution time: 0.103 ms
(6 filas)
Thank you for your help.
Regards.