btree_gin, bigint and number literals

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