int8range and index usage for <@ operator

[KOPOSOV Sergey]

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()*100)::bigint,(random()*100)::bigint  from
generate_series(0,100);
INSERT 0 101
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=100.00..1017906.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=100.00..1017906.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,9)}'::int8multirange) ;

That correctly produces the query plan with bitmap index

  QUERY
PLAN

---
 Bitmap Heap Scan on xtmp  (cost=373.79..6050.55 rows=1 width=16)
   Recheck Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,9)}'::int8multirange)
   ->  Bitmap Index Scan on xtmp_int8range_idx  (cost=0.00..371.29 rows=1
width=0)
 Index Cond: (int8range(a, (a + 1)) &&
'{[3,27),[100,9)}'::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.

Re: int8range and index usage for <@ operator

[KOPOSOV Sergey]

On Thu, 2022-04-28 at 12:57 -0400, Tom Lane wrote:
> This email was sent to you by someone outside the University.
> You should only click on links or attachments if you are certain that the
> email is genuine and the content is safe.
>
> KOPOSOV Sergey  writes:
> > 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
>
> There's no support for that at the moment.  In principle the range
> case could be converted to something like "integer_column >= lower_value
> AND integer_column <= upper_value" by attaching a support function to
> <@ and implementing the SupportRequestIndexCondition API.  I think it
> could only work for a plan-time-constant range though, else you'd not know
> whether to use equality or inequality bounds.  (Hmm ... or maybe, use
> equality always and treat it as a lossy conversion?  But infinite bounds
> would still be a headache.)

Thanks for your reply. I will refresh my memory about support functions.

In my case I am certainly thinking of a situation where I'd like to do

integer_column <@ Some_Function()

where Some_function() is a a complicated C function returning a bunch of integer
ranges at runtime depending on arguments.

   S


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.