Re: Index usage for elem-contained-by-const-range clauses

On Sunday 12 March 2017 01:58 AM, Jim Nasby wrote:
> On 3/10/17 8:29 AM, Alexander Korotkov wrote: >> That's cool idea. But I would say more. Sometimes it's useful to >> transform "intcol between x and y" into "intcol <@ 'x,y'::int4range". >> btree_gin supports "intcol between x and y" as overlap of "intcol >= x" >> and "intcol <= y". That is very inefficient. But it this clause would >> be transformed into "intcol <@ 'x,y'::int4range", btree_gin could handle >> this very efficient. > > That's certainly be nice as well, but IMHO it's outside the scope of this patch to accomplish that.

Also, I think btree indexes are more common than btree_gin. The motivation for
this originally came from trying to use the primary key of a large table in a
range search, and the primary key index was the default btree.

Also, this is my first deep dive into Postgres's source code, so I took a few
easy ways out, just to get started. If it's not too complex to get btree_gin to
handle between queries as contained-in-range, I can give it a try.

> > BTW, while we're wishing for things... Something else that would be nice is if there was a way to do these kind of transforms without hacking the backend...

Indeed. And this was one of the things Tom said back when a similar discussion
had happened (on the -performance mailing list). But seeing as how it's been
almost four years since then, I decided to go ahead with the backend hacking
anyway.

> >> Also, I noticed that patch haven't regression tests. > > BTW, those tests need to pay special attention to inclusive vs exclusive bounds.

I will add regression tests, though I do have to get through all of Tom's
suggestions elsewhere in this thread first.

--
#!/usr/bin/env regards
Chhatoi Pritam Baral