Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.
David Pereiro Lagares <david@nlpgo.com>
From: David Pereiro Lagares <david@nlpgo.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2018-01-17T17:34:54Z
Lists: pgsql-bugs, pgsql-hackers
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.
Commits
-
Improve stability of recently-added regression test case.
- afb5fb290e90 10.6 landed
- 6d2c760b059e 11.1 landed
- 4f6a77e46af2 9.5.15 landed
- 3e406417b8da 9.6.11 landed
- 17d6a8fb764c 12.0 landed
-
Fix IOS planning when only some index columns can return an attribute.
- be55bfc9386d 9.5.13 landed
- b5febc1d125c 11.0 landed
- 3f26be83ec1e 9.6.9 landed
- 147b59971eba 10.4 landed
-
Ability to advance replication slots
- 9c7d06d60680 11.0 cited
-
Allow no-op GiST support functions to be omitted.
- d3a4f89d8a3e 11.0 cited