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
