Message-ID: <1516210494.1798.16.camel@nlpgo.com>
Subject: Index-only scan returns incorrect results when using a composite
 GIST index with a gist_trgm_ops column.
From: David Pereiro Lagares <david@nlpgo.com>
To: pgsql-bugs@lists.postgresql.org
Date: Wed, 17 Jan 2018 18:34:54 +0100
Organization: nlpgo
Content-Type: multipart/signed; micalg="pgp-sha256";
	protocol="application/pgp-signature"; boundary="=-QE87JzOG/yQA5uptTnHb"
Mime-Version: 1.0
Precedence: bulk


--=-QE87JzOG/yQA5uptTnHb
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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:=20

Setup:

        root=3D# SELECT version();
       =20
        version                                             =20
        ---------------------------------------------------------------
        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)
       =20
       =20
        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');
       =20
       =20
Queries that make a seq scan yield correct results:
       =20
        root=3D# SELECT w FROM words WHERE w LIKE '%e%';=20
           w  =20
        -------
         Lorem
        (1 fila)
       =20
        root=3D# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
                                                  QUERY PLAN         =20
        --------------------------------------------------------------
        Seq Scan on words  (cost=3D0.00..1.02 rows=3D2 width=3D6) (actual
        time=3D0.018..0.020 rows=3D1 loops=3D1)
           Filter: ((w)::text ~~ '%e%'::text)
           Rows Removed by Filter: 1
         Planning time: 0.112 ms
         Execution time: 0.040 ms
        (5 filas)
       =20
Index scan with simple index works fine also:

        root=3D# SET enable_seqscan =3D OFF;
        SET
        root=3D# CREATE INDEX ON words USING GIST(w gist_trgm_ops);
        CREATE INDEX
        root=3D# SELECT w FROM words WHERE w LIKE '%e%';
           w  =20
        -------
         Lorem
        (1 fila)
       =20
        root=3D# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
                                                             QUERY
        PLAN  =20
        ------------------------------------------------------------------
        Index Scan using words_w_idx on words  (cost=3D0.13..8.16 rows=3D2
        width=3D32) (actual time=3D0.053..0.054 rows=3D1 loops=3D1)
           Index Cond: ((w)::text ~~ '%e%'::text)
           Rows Removed by Index Recheck: 1
         Planning time: 0.101 ms
         Execution time: 0.114 ms
        (5 filas)
       =20
Queries that use the index only scan return no results:

        root=3D# CREATE INDEX ON words USING GIST(w gist_trgm_ops, w);
        CREATE INDEX
        root=3D# VACUUM ANALYZE words;
        VACUUM
        root=3D# SELECT w FROM words WHERE w LIKE '%e%';
         w=20
        ---
        (0 filas)
       =20
        root=3D# EXPLAIN ANALYZE SELECT w FROM words WHERE w LIKE '%e%';
                                                                QUERY
        PLAN =20
        -------------------------------------------------------------------=
- Index Only Scan using words_w_w1_idx on words  (cost=3D0.13..4.16 rows=3D=
2 width=3D6) (actual time=3D0.043..0.043 rows=3D0 loops=3D1)
           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)
       =20
Thank you for your help.
Regards.
       =20
       =20

--=-QE87JzOG/yQA5uptTnHb
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2

iQEcBAABCAAGBQJaX4k+AAoJEPuUZKOdt8h8pnIIALEHt+7i3hxMCsakFUdJAEf/
x4cOxIf54AvpcBSDERG4y6qjsvNUUcEUiDT8V4yqBRudNuEVijJbiSsPC6y/wl4C
1hHfY5a84gAJOoIJSDMtigqM2PmskiwdKybOa+Asll6kxUbGg12r2S27MGg/XbZ+
prly7eBaDGUm9bUHjbsA620uEHz9/cESsIqAbx8tzv9MW8b4NhQLqS38Q8B4mBmx
D/qgrDQcAQnmT4ElTfAlMSsebKFnb1OMvXmaK5I/znkIJDgfpgAjWnL2sRACiDUT
zDIbD3T767Kn2Th6jxq4D41hXkEoYFWlDIqAa2/obNrgCMhf5ebaqu5M22ogKME=
=4+yn
-----END PGP SIGNATURE-----

--=-QE87JzOG/yQA5uptTnHb--