I have table tbl with columns
- data TEXT
- fk_id BIGINT
- text TEXT
There is over 100M records, and ~1K different values for fk_id. I need to run query like this
SELECT * FROM tbl WHERE fk_id=12345 AND text LIKE '%abcdef%
I tried to use extension GIN index with btree_gin and gin_trgm_ops
CREATE EXTENSION pg_trgm;
CREATE EXTENSION btree_gin;
CREATE INDEX on tbl USING GIN (fk_id, text gin_trgm_ops)
But query analyzer ignore column fk_id for query
explain select * from tbl where fk_id = 12345 and text like '%abcdef%' limit 10;
Limit (cost=28.00..32.02 rows=1 width=90)
-> Bitmap Heap Scan on tbl (cost=28.00..32.02 rows=1 width=90)
Recheck Cond: (text ~~ '%abcdef%'::text)
Filter: (fk_id = 12345)
-> Bitmap Index Scan on table_fk_id_text_idx (cost=0.00..28.00 rows=1 width=0)
Index Cond: (text ~~ '%abcdef%'::text)
But if I use INT type instead of BIGINT for column fk_id, index will work as I expect
tbl
- data TEXT
- fk_id INT
- text TEXT
explain select * from tbl where fk_id = 12345 and text like '%abcdef%' limit 10;
Limit (cost=36.00..40.02 rows=1 width=90)
-> Bitmap Heap Scan on tbl (cost=36.00..40.02 rows=1 width=90)
Recheck Cond: ((fk_id = 12345) AND (text ~~ '%abcdef%'::text))
-> Bitmap Index Scan on tbl_fk_id_text_idx (cost=0.00..36.00 rows=1 width=0)
Index Cond: ((fk_id = 12345) AND (text ~~ '%abcdef%'::text))
So, I can use GIN with INT, but cannot use GIN with BIGINT. Why I cannot use BIGINT column for GIN index? Documentation says, that btree_gin works with int4(INT) and with int8(BIGINT). Maybe there is another way to solve this problem?
