Re: Pushing ScalarArrayOpExpr support into the btree index AM

Florian Pflug <fgp@phlo.org> writes:
> On Oct15, 2011, at 20:58 , Tom Lane wrote:
>> So, at least as far as btrees are concerned, it seems like I implemented
>> the ScalarArrayOpExpr logic at the wrong level and it ought to be pushed
>> down into the index AM.  The above rules seem pretty btree-specific, so
>> I don't think that we ought to have the main executor doing any of this.
>> I envision doing this by marking btree as supporting ScalarArrayOpExpr
>> scankeys directly, so that the executor does nothing special with them,
>> and the planner treats them the same as regular scalar indexquals.

> Hm, would this make it possible to teach the array GIN ops to also handle
> ScalarArrayOpExpr?

Hmm, maybe.  In principle the index AM can always do this at least as
efficiently as the executor can, and maybe there's actual wins to be had
in GIST and GIN.  So another route to getting rid of the executor-level
support would be to implement ScalarArrayOpExpr in all the AMs.  I'm not
personally volunteering to do that though.

> I've recently had to put
>   ARRAY[$1] <@ $2 AND $1 = ANY($2)
> into an (inlineable) SQL function to make it use a (btree) index if
> $1 is a scalar-values field (and $1 constant array) and a GIN index if $2 
> is a GIN-indexed array-values field (and $2 a constant array). Which of
> course sucks from an efficiency POV.

That doesn't seem like the same thing at all, because the indexed column
is on different sides of the expression in the two cases.  The situation
that I'm worried about is "indexedcolumn op ANY(arrayconstant)", and
what you're bringing up is "constant op ANY(indexedarraycolumn)".  To
fit the latter into the existing opclass infrastructure, we'd have to
somehow teach the planner that "constant op ANY(indexedarraycolumn)"
is interchangeable with "indexedarraycolumn @> constant", for pairs of
operators where that's indeed the case.  Seems like that'd be a lot
messier than the use-case warrants.
        regards, tom lane