Hi,
I'm trying to understand to is there a possibility to use an index for PG
when I have a integer column in a table and I want to execute queries with this
integer_column <@ int8range
or
integer_column <@ int8multirange
in a where clause
Here is an example
***********
wsdb=> create temp table xtmp (a bigint, b bigint);
CREATE TABLE
wsdb=> insert INTO xtmp select
(random()*10000000000)::bigint,(random()*10000000000)::bigint from
generate_series(0,1000000);
INSERT 0 1000001
wsdb=> create index ON xtmp(a);
CREATE INDEX
wsdb=> create index ON xtmp using gist (a);
CREATE INDEX
wsdb=> analyze xtmp;
ANALYZE
wsdb=> explain select * from xtmp where a <@ int8range(4,10);
QUERY PLAN
------------------------------------------------------------
Seq Scan on xtmp (cost=0.00..17906.01 rows=5000 width=16)
Filter: (a <@ '[4,10)'::int8range)
(2 rows)
wsdb=> set enable_seqscan to off;
SET
wsdb=> explain select * from xtmp where a <@ int8range(4,10);
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on xtmp (cost=10000000000.00..10000017906.01 rows=5000 width=16)
Filter: (a <@ '[4,10)'::int8range)
JIT:
Functions: 2
Options: Inlining true, Optimization true, Expressions true, Deforming true
(5 rows)
***************
Note that the <@ operator does not use an index.
Obviously with the int8range that is maybe superfluous usage of int8range
instead of greater/smaller operators, but I'm interested in queries involving
multi-range like queries which also do not seem to use the index
select * from xtmp where a <@ ('{[3,7), [8,9)}'::int8multirange) ;
postgres=# explain select * from xtmp where a <@ ('{[3,7),
[8,9)}'::int8multirange) ;;
QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on xtmp (cost=10000000000.00..10000017906.01 rows=5000 width=16)
Filter: (a <@ '{[3,7),[8,9)}'::int8multirange)
(2 rows)
I do know that I can solve the issue by creating a functional index on a 'dummy
range' like this:
postgres=# create index ON xtmp using gist (int8range(a,a+1));
and execute queries like this:
postgres=# explain select * from xtmp where int8range(a,a+1) && ('{[3,27),
[100,11119)}'::int8multirange) ;
That correctly produces the query plan with bitmap index
QUERY
PLAN
--------------------------------------------------------------------------------
-------
Bitmap Heap Scan on xtmp (cost=373.79..6050.55 rows=10000 width=16)
Recheck Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,11119)}'::int8multirange)
-> Bitmap Index Scan on xtmp_int8range_idx (cost=0.00..371.29 rows=10000
width=0)
Index Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,11119)}'::int8multirange)
(4 rows)
But is there a way to avoid creating this dummy index on int8range consisting of
one element ? I somehow would have expected that integer <@ int8range operation
should use the index.
Thanks in advance,
Sergey
PS For the test I've been using PG14.2
The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.
