The following bug has been logged online:
Bug reference: 6092
Logged by: Jeff Frost
Email address: jeff@pgexperts.com
PostgreSQL version: 9.0.4
Operating system: CentOS 5.5
Description: specific casting required for gist indexing of bigint
Details:
Ran into a situation with a customer who is using the btree_gist contrib
module to allow combined index of some tsearch data and two other columns.
One of these other columns is a bigint field. I noticed that the combined
index won't be used by the planner unless you specifically cast the bare
number to a bigint. Here's a quick test case:
createdb jefftest
psql -f /usr/pgsql-9.0/share/contrib/btree_gist.sql jefftest
jefftest=# create table test (id bigint);
CREATE TABLE
Time: 28.541 ms
jefftest=# insert into test select generate_series(1,100000);
INSERT 0 100000
Time: 179.768 ms
jefftest=# CREATE INDEX test__id ON test using gist(id) ;
CREATE INDEX
Time: 1603.811 ms
jefftest=# ANALYZE test;
ANALYZE
Time: 21.854 ms
jefftest=# explain analyze select id from test WHERE id = 587;
QUERY PLAN
----------------------------------------------------------------------------
------------------------
Seq Scan on test (cost=0.00..1693.00 rows=500 width=8) (actual
time=0.097..14.698 rows=1 loops=1)
Filter: (id = 587)
Total runtime: 14.739 ms
(3 rows)
Time: 32.965 ms
jefftest=# explain analyze select id from test WHERE id = 587::bigint;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------
Bitmap Heap Scan on test (cost=20.16..490.49 rows=500 width=8) (actual
time=0.037..0.038 rows=1 loops=1)
Recheck Cond: (id = 587::bigint)
-> Bitmap Index Scan on test__id (cost=0.00..20.03 rows=500 width=0)
(actual time=0.027..0.027 rows=1 loops=1)
Index Cond: (id = 587::bigint)
Total runtime: 0.080 ms
(5 rows)
Time: 0.592 ms