On Mon, 03 Dec 2018 11:47:17 -0500 Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > Jan Behrens <jbe@xxxxxxxxxxxxxxxxxxxxxxxxx> writes: > > > However, the GiST index seems not to work as expected by me when > > 64-bit integers are involved. I tried to create a minimal > > proof-of-concept to demonstrate this. Consider the following setup: > > > > CREATE TABLE test8_gist (id SERIAL4, ctx INT8); > > CREATE INDEX ON test8_gist USING gist (ctx, id); > > EXPLAIN SELECT * FROM test8_gist WHERE ctx = 1 AND id = 2; > > -- uses Index Cond: (id = 2) > > > > The query planning for the select on table "test8_gist" does not > > include "ctx" in the "Index Cond". > > Probably it would if you'd written "WHERE ctx = 1::int8". Without > the cast, what you'll have is "int8 = int4", and I suspect that > btree_gist doesn't include cross-type operators in its opclasses. > > regards, tom lane You are right! I just tested it and ::int8 does the job. It might be good to add a short notice or warning in the documentation at: https://www.postgresql.org/docs/current/btree-gist.html It might help other people who run into the same problem. Thanks for helping me, Jan Behrens -- Public Software Group e. V. Johannisstr. 12, 10117 Berlin, Germany www.public-software-group.org vorstand@xxxxxxxxxxxxxxxxxxxxxxxxx eingetragen in das Vereinregister des Amtsgerichtes Charlottenburg Registernummer: VR 28873 B Vorstände (einzelvertretungsberechtigt): Jan Behrens Axel Kistner Andreas Nitsche Björn Swierczek
