index using btree_gin on smallint/bigint

Question

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

Answer 1

It looks like btree_gin is not as clever about reasoning about datatypes as the regular btree code is. If you explicitly cast your query condition constant to be of the exact same type as the index, then it will work.

explain select * from test where year = 2000::smallint;
                                QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=467.50..56278.04 rows=50000 width=12)
   Recheck Cond: (year = '2000'::smallint)
   ->  Bitmap Index Scan on test_year_idx  (cost=0.00..455.00 rows=50000 width=0)
         Index Cond: (year = '2000'::smallint)