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

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?
Also, I noticed that patch haven't regression tests. Some mention of this
optimization in docs is also nice to have.

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