Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.

From: David Pereiro Lagares <david(at)nlpgo(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.
Date: 2018-01-17 17:34:54
Message-ID: 1516210494.1798.16.camel@nlpgo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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.


Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Ratnakar Tripathy 2018-01-17 17:48:52 Re: BUG #14947: Installation Errors
Previous Message PG Bug reporting form 2018-01-17 14:26:22 BUG #15013: JNI-JDBC: org.postgresql.util.PSQLException: FEHLER: ungültiges Message-Format

Browse pgsql-hackers by date

  From Date Subject
Next Message Victor Wagner 2018-01-17 17:36:48 Re: master make check fails on Solaris 10
Previous Message Peter Eisentraut 2018-01-17 17:30:16 Re: [HACKERS] GnuTLS support