BUG #6092: specific casting required for gist indexing of bigint
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
"Jeff Frost" <jeff@pgexperts.com> writes:
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.
If memory serves, the btree_gist opclasses don't include any cross-type
operators, so "int8 = int4" doesn't work here.
regards, tom lane
On 07/05/11 17:06, Tom Lane wrote:
"Jeff Frost" <jeff@pgexperts.com> writes:
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.If memory serves, the btree_gist opclasses don't include any cross-type
operators, so "int8 = int4" doesn't work here.
Ah! And if you look at the contrib/btree_gist/sql/int8.sql test file, you'll
see this:
SELECT count(*) FROM int8tmp WHERE a < 464571291354841::int8;
So, I'd say it's expected behavior even though it's slightly counter intuitive
if you're used to the auto typing behavior.
Jeff Frost <jeff@pgexperts.com> writes:
On 07/05/11 17:06, Tom Lane wrote:
"Jeff Frost" <jeff@pgexperts.com> writes:
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.
If memory serves, the btree_gist opclasses don't include any cross-type
operators, so "int8 = int4" doesn't work here.
Ah! And if you look at the contrib/btree_gist/sql/int8.sql test file, you'll
see this:
SELECT count(*) FROM int8tmp WHERE a < 464571291354841::int8;
So, I'd say it's expected behavior even though it's slightly counter intuitive
if you're used to the auto typing behavior.
Well, it might be nice to fix it sometime, but I'd characterize it as an
unimplemented feature in btree_gist, not a bug.
regards, tom lane