Обсуждение: 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

"Quentin de Metz" <quentin@de.me.tz> writes:
> On a multi-column GIN index over a bigint column and a text column, the query planner does not filter the index on
thebigint column when a condition on this column is specified with a number literal. 

Yeah, because "owner_id = 12" will use int84eq, which as you observe
is not supported by btree_gin's opclass.

> Would you be open to considering a patch to include the ALTER OPERATOR snippet in the btree_gin install script, so
thatthis works out of the box? 

I'd be quite surprised if that "just works" without any corresponding
changes in the C code, because btree_gin.c only knows about applying
same-type-on-both-sides comparison functions.  (int8 vs int4 might
appear to work as long as you don't try very hard, but for example
it'd fail on 32-bit or big-endian hardware.)  If you feel like writing
a patch that actually takes care of the matter fully, step right up.

            regards, tom lane