int8range and index usage for <@ operator
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.
KOPOSOV Sergey <Sergey.Koposov@ed.ac.uk> 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.)
regards, tom lane
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 <Sergey.Koposov@ed.ac.uk> 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 clauseThere'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.