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
Следующее
От: Ratnakar Tripathy
Дата:
Сообщение: Re: BUG #14947: Installation Errors