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.
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 |
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 |