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 bigint column 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 this works out of the box?

Quentin de Metz