Re: Pushing ScalarArrayOpExpr support into the btree index AM

On Oct16, 2011, at 20:26 , Tom Lane wrote:
> Florian Pflug <fgp(at)phlo(dot)org> writes:
>> On Oct16, 2011, at 19:09 , Tom Lane wrote:
>>> 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)".
>
>> 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?
>
> Well, no, unless you're proposing to somehow implement the "constant op
> ANY(indexedarraycolumn)" case in all the AMs. I don't see any practical
> way to do it in btree, for one.

Hm, true. Also, the "operator belongs to the index's opclass" part of the
push-down condition I proposed was wrong anyway, because the "=" operator
in e.g.

3 = ANY(indexed_int_array_column)

isn't in the opclass int_array_ops. From that, it seems that what would be
needed to make the planner use a GIN index to evaluate the qual above is a
a way for it to realize that there's a connection between some GIN indices
(like *_array_ops) and btree opclasses on the GIN index's storage type. Which
would be nice, but I see now that it has little to do with your proposal,
which is only concerned with operators from the index's opclass.

best regards,
Florian Pflug