On Mon, 03 Dec 2018 11:47:17 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jan Behrens <jbe@public-software-group.org> 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@public-software-group.org
eingetragen in das Vereinregister
des Amtsgerichtes Charlottenburg
Registernummer: VR 28873 B
Vorstände (einzelvertretungsberechtigt):
Jan Behrens
Axel Kistner
Andreas Nitsche
Björn Swierczek
