Thread: 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