Feedback-ID: i7a6842ed:Fastmail
MIME-Version: 1.0
Date: Fri, 31 Jan 2025 18:02:13 +0100
From: "Quentin de Metz" <quentin@de.me.tz>
To: pgsql-novice@postgresql.org
Message-Id: <58782480-ab75-4416-a177-ccf91be288a9@app.fastmail.com>
Subject: btree_gin, bigint and number literals
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/58782480-ab75-4416-a177-ccf91be288a9%40app.fastmail.com>
Precedence: bulk

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