Re: Pushing ScalarArrayOpExpr support into the btree index AM

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

At the time I didn't see a way to easily teach GIN to support ANY, but
with your proposal it seems entirely doable. Unless I'm missing something,
that is.

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

Hm, if the number of box values isn't fixed, the ANY form plays much nicer
nicer with parametrized statements than the OR form. So I think we shouldn't
take that away from people.

OTOH it seems that, depending on the actual list of box values, a bitmap
indexscan isn't the smartest way to do this. If the boxes overlap (or are
close enough to each other), using the bounding box to search the index
and then filtering the remaining rows ought to be more efficient.

So maybe we should remove the support for ScalarArrayOpExpr from the main
executor, but add support for it to GIST?

best regards,
Florian Pflug