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

On Sat, Mar 18, 2017 at 12:41 AM, Pritam Baral <pritam(at)pritambaral(dot)com>
wrote:

> On Friday 10 March 2017 07:59 PM, Alexander Korotkov wrote:
>
>> Hi, Pritam! > > I've assigned to review this patch. > > On Thu, Feb 23,
>> 2017 at 2:17 AM, Pritam Baral <pritam(at)pritambaral(dot)com> wrote: > >
>> The topic has been previously discussed[0] on the -performance mailing
>> list, > about four years ago. > > In that thread, Tom suggested[0]
>> the planner could be made to "expand > "intcol <@ >
>> 'x,y'::int4range" into "intcol between x and y", using something similar
>> > to the > index LIKE optimization (ie, the "special operator"
>> stuff in indxpath.c)". > > > 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. > > > > This patch tries to do
>> exactly that. It's not tied to any specific datatype, > and has
>>
> been tested with both builtin types and custom range types. Most > of
> the > checking for proper datatypes, operators, and btree index happens
> before > this > code, so I haven't run into any issues yet in my
> testing. But I'm not > familiar > enough with the internals to be
> able to confidently say it can handle > all cases > just yet. > > >
> I've tried this patch. It applies cleanly, but doesn't compile. > >
> indxpath.c:4252:1: error: conflicting types for
> 'range_elem_contained_quals' > range_elem_contained_quals(Node *leftop,
> Datum rightop) > ^ > indxpath.c:192:14: note: previous declaration is here
> > static List *range_elem_contained_quals(Node *leftop, Oid expr_op, Oid
> opfamily, > ^ > Could you please recheck that you published
> right version of patch?
>
> So sorry. I'm attaching the correct version of the original with this,
> in case you want to test the limited implementation, because I still
> have to go through Tom's list of suggestions.
>
> BTW, the patch is for applying on top of REL9_6_2, and while I
> suspect it may work on master too, I haven't tested it since the
> original submission (Feb 23).

What is idea behind basing patch on the REL9_6_2?
This patch implements new functionality and it's definitely not going to be
considered to be committed to stable release branches.
If you are interesting in committing this patch to master, please rebase it
on master branch. If not, please clarify the purpose of this submission.

Also, please include some numbering to the patch name, so that we could
distinguish one version of patch from another.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company