int8range and index usage for <@ operator - Mailing list pgsql-general

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
buidheanncarthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.