Re: subquery returning array

Zeljko Vrba <zvrba(at)ifi(dot)uio(dot)no> writes:

> Is there yet another way of making WHERE field = ANY (subselect
> returning an array) work? Or make postgres to use index?

You could use the int_array_enum() function from the contrib/int_agg module.

> Also, what is the limit on the number of elements in the IN (...) condition
> before the database resorts to sequential scan?

It depends on the table. If, for example, there's little free space in your
table and the records are very narrow then sequential scans will be especially
efficient since each i/o will read in many records. An index scan of any
significant size would likely have to read nearly every page in multiple
times.

The problem in your case is that Postgres has no idea how large an array it's
going to find when it's doing the planning. I'm not sure what the fallback
logic is, apparently it's assuming a fairly low selectivity for the =ANY
constraint.

Actually I think there was some discussion a while back about making =ANY a
little less pessimistic about the selectivity. Perhaps 8.1 will be better for
this. If you're still in development with a long timeframe you could try a CVS
build to see if that's the case.

--
greg