Index-only scan returns incorrect results when using a compositeGIST index with a gist_trgm_ops column.
От | David Pereiro Lagares |
---|---|
Тема | Index-only scan returns incorrect results when using a compositeGIST index with a gist_trgm_ops column. |
Дата | |
Msg-id | 1516210494.1798.16.camel@nlpgo.com обсуждение исходный текст |
Ответы |
Re: Index-only scan returns incorrect results when using a composite GIST index with a gist_trgm_ops column.
(Sergei Kornilov <sk@zsrv.org>)
Re: Index-only scan returns incorrect results when using acomposite GIST index with a gist_trgm_ops column. (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>) Re: Index-only scan returns incorrect results when using acomposite GIST index with a gist_trgm_ops column. (Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>) |
Список | pgsql-bugs |
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.
Вложения
В списке pgsql-bugs по дате отправления:
Предыдущее
От: PG Bug reporting formДата:
Сообщение: BUG #15013: JNI-JDBC: org.postgresql.util.PSQLException: FEHLER: ungültiges Message-Format