Hi Hackers,
I noticed that this query wasn't using my GiST index:
postgres=# create extension btree_gist;
CREATE EXTENSION
postgres=# create table t (id bigint, valid_at daterange, exclude using gist (id with =, valid_at
with &&));
CREATE TABLE
postgres=# explain select * from t where id = 5;
QUERY PLAN
---------------------------------------------------
Seq Scan on t (cost=0.00..25.00 rows=6 width=40)
Filter: (id = 5)
(2 rows)
But if I add a cast to bigint, it does:
postgres=# explain select * from t where id = 5::bigint;
QUERY PLAN
---------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=4.19..13.66 rows=6 width=40)
Recheck Cond: (id = '5'::bigint)
-> Bitmap Index Scan on t_id_valid_at_excl (cost=0.00..4.19 rows=6 width=0)
Index Cond: (id = '5'::bigint)
(4 rows)
There is a StackOverflow question about this with 5 upvotes, so it's not just me who was surprised
by it.[1]
The reason is that btree_gist only creates pg_amop entries for symmetrical operators, unlike btree
which has =(int2,int8), etc. So this commit adds support for all combinations of int2/int4/int8 for
all five btree operators (</<=/=/>=/>). After doing that, my query uses the index without a cast.
One complication is that while btree has just one opfamily for everything (integer_ops), btree_gist
splits things up into gist_int2_ops, gist_int4_ops, and gist_int8_ops. So where to put the
operators? I thought it made the most sense for a larger width to support smaller ones, so I added
=(int2,int8) and =(int4,int8) to gist_int8_ops, and I added =(int2,int4) to gist_int4_ops.
[1] https://stackoverflow.com/questions/71788182/postgres-not-using-btree-gist-index
Yours,
--
Paul ~{:-)
pj@illuminatedcomputing.com
