Обсуждение: 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