Re: int8range and index usage for <@ operator

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