Hi all,
On a multi-column GIN index over a bigint column and a text column, the query planner does not filter the index on the
bigintcolumn when a condition on this column is specified with a number literal.
Please find below a simple reproduction script:
CREATE TABLE cars (owner_id BIGINT, license_plate TEXT);
INSERT INTO cars (owner_id, license_plate)
SELECT i % 100, md5(random()::text)
FROM generate_series(1, 10000) AS t(i);
CREATE EXTENSION btree_gin;
CREATE EXTENSION pg_trgm;
CREATE INDEX testidx ON cars USING GIN (owner_id, license_plate gin_trgm_ops);
-- below, we see that the Index Cond does not take the owner_id condition into account
EXPLAIN SELECT * FROM cars WHERE owner_id = 12 AND license_plate ILIKE '%abc%';
QUERY PLAN
-------------------------------------------------------------------------
Bitmap Heap Scan on cars (cost=13.32..112.93 rows=1 width=41)
Recheck Cond: (license_plate ~~* '%abc%'::text)
Filter: (owner_id = 12)
-> Bitmap Index Scan on testidx (cost=0.00..13.32 rows=101 width=0)
Index Cond: (license_plate ~~* '%abc%'::text)
-- when explicitly casting the number literal to bigint, the Index Cond takes it into account
EXPLAIN SELECT * FROM cars WHERE owner_id = 12::bigint AND license_plate ILIKE '%abc%';
QUERY PLAN
---------------------------------------------------------------------------------------
Bitmap Heap Scan on cars (cost=21.52..25.54 rows=1 width=41)
Recheck Cond: ((owner_id = '12'::bigint) AND (license_plate ~~* '%abc%'::text))
-> Bitmap Index Scan on testidx (cost=0.00..21.52 rows=1 width=0)
Index Cond: ((owner_id = '12'::bigint) AND (license_plate ~~* '%abc%'::text))
-- the more permanent solution seems to be altering the operator type
ALTER OPERATOR FAMILY int8_ops USING gin ADD
OPERATOR 1 < (int8, int4) ,
OPERATOR 2 <= (int8, int4) ,
OPERATOR 3 = (int8, int4) ,
OPERATOR 4 >= (int8, int4) ,
OPERATOR 5 > (int8, int4) ,
FUNCTION 1 (int8, int4) btint84cmp(int8, int4);
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on cars (cost=21.52..25.54 rows=1 width=41)
Recheck Cond: ((owner_id = 12) AND (license_plate ~~* '%abc%'::text))
-> Bitmap Index Scan on testidx (cost=0.00..21.52 rows=1 width=0)
Index Cond: ((owner_id = 12) AND (license_plate ~~* '%abc%'::text))
Would you be open to considering a patch to include the ALTER OPERATOR snippet in the btree_gin install script, so that
thisworks out of the box?
Quentin de Metz
