Add GiST support for mixed-width integer operators
От | Paul Jungwirth |
---|---|
Тема | Add GiST support for mixed-width integer operators |
Дата | |
Msg-id | b05c739d-e2d4-4919-9691-69b2709d3546@illuminatedcomputing.com обсуждение исходный текст |
Ответы |
Re: Add GiST support for mixed-width integer operators
|
Список | pgsql-hackers |
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
Вложения
В списке pgsql-hackers по дате отправления: