create table test(
id serial primary key,
tagged smallint[]
);
There is gin index on tagged
column, with _int2_ops
operator class:
CREATE INDEX ix ON test USING GIN(col _int2_ops);
When I run this query:
select * from test
where tagged @> ARRAY[11]
order by id limit 100;
EXPLAIN ANALYZE
shows:
Limit (cost=0.43..19524.39 rows=100 width=36) (actual time=25024.124..25027.263 rows=100 loops=1) -> Index Scan using test_pkey on test (cost=0.43..508404.37 rows=2604 width=36) (actual time=25024.121..25027.251 rows=100 loops=1) Filter: ((tagged)::integer[] @> '{11}'::integer[]) Rows Removed by Filter: 2399999 Planning time: 6.912 ms Execution time: 25027.307 ms
Bold emphasis mine. Why is the tagged
column converted to integer[]
type? I think this is the reason why GIN the index isn't used and the query runs slow.
I tried WHERE tagged @> ARRAY[11]::smallint[]
but got this error:
operator is not unique: smallint[] @> smallint[]
If I do the same but use tagged int[]
and create index as
CREATE INDEX ix ON test USING GIN(tagged gin__int_ops);
then the above query uses the GIN index:
"-> Bitmap Index Scan on ix (cost=0.00..1575.53 rows=2604 width=0) (actual time=382.840..382.840 rows=2604480 loops=1)" " Index Cond: (tagged @> '{11}'::integer[])"
This is a bit faster than previous, but It takes on average 10 second - still too slow. I want to try smallint[]
instead of int[]
, maybe that will be faster ...