Re: Pushing ScalarArrayOpExpr support into the btree index AM

On Oct16, 2011, at 19:09 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)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.

Hm, that sounds like we ought to leave the existing infrastructure in
the main executor in place until we have GIN and GIST support.

>> 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)".

Hm, true

> 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.

That exactly was what convinced me previously that there's no easy way
to do this. I had hoped that with your patch only the index AMs, instead of
the planner, need to know about these equivalences.

Couldn't we teach the main executor to push a ScalarArrayOpExpr down
into the index AM if the operator belongs to the index's opclass, one
side is indexed, and the other is constant?

best regards,
Florian Pflug