Re: Pushing ScalarArrayOpExpr support into the btree index AM

On Sat, Oct 15, 2011 at 02:58:45PM -0400, Tom Lane wrote:
> [algorithm for a regular index scan satisfying "key IN (...)"]

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

Sounds sensible. The algorithm applies to more than ScalarArrayOpExpr; is it
not the ability to handle an OR'ed list of ScanKey instead of an AND'ed one?
Would it be worth exposing the capability along those lines instead, even if the
only initial consumer is ScalarArrayOpExpr?

> In principle somebody could be doing something like
> WHERE pointcol <@ ANY (ARRAY[list of box values])
> and expecting that to generate a bitmap indexscan on a GIST index, but
> is it likely that anyone is doing that? (As opposed to the equivalent
> formulation with "pointcol <@ box1 OR pointcol <@ box2 ...", which would
> still work to generate OR'd bitmap scans even if we took out the
> ScalarArrayOpExpr logic.)

Removing the "key IN (...)" optimization for hash indexes would add one more
barrier to making that access method practical.