I try to use a btree_gin index on smallint column, but the index is never used for queries. According to documentation (http://www.postgresql.org/docs/9.5/static/btree-gin.html) it should work. If I change the column-type to int the index is used, if it is bigint it does not work either.
I would like to use btree_gin as the index is much smaller. In this example the table is 442MB, btree_bin index is 14MB, normal btree index is 224MB in size.
Steps to reproduce:
create table test (year smallint, somedata varchar);
insert into test (year, somedata) select (random()*50) + 1970 as year, 'something' as somedata from generate_series(1,10000000);
create extension btree_gin;
create index on test using gin (year);
The query (type smallint, index is not used):
explain select * from test where year = 2000;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on test (cost=0.00..179055.00 rows=192333 width=12)
Filter: (year = 2000)
Change the type to int:
alter table test alter column year type int;
Now the index is used:
explain select * from test where year = 2000;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=467.50..56278.04 rows=50000 width=14)
Recheck Cond: (year = 2000)
-> Bitmap Index Scan on test_year_idx (cost=0.00..455.00 rows=50000 width=0)
Index Cond: (year = 2000)
This happens with Postgres 9.5.0 on Debian strech and Postgres 9.4.2 on Centos 6.4.
Could anybody please explain how to use btree_gin on smallint columns? Thank you