Обсуждение: Using ANY()

Hi all,

Is it appropriate to use ANY() in a select statement as so?

SELECT * FROM table t WHERE t.id = ANY(ARRAY[1,2,3]);

A less trivial usage of the above would be to pass an array to a simple
function using it to return a range of arbitrary rows. The alternative to
this would be to (programmatically) call the function multiple times on a
list of arguments. Some questions:

1) How does ANY() behave on indexed columns?

2) How does ANY() behave when passed an array with one element?

3) Generally is it better to use ANY on a passed ARRAY, or to just call a
select multiple times (and aggregate the results)? Is ANY just a glorified
OR?

Shak

On Sun, 2009-06-07 at 19:33 +0100, Shakil Shaikh wrote:
> Hi all,
>
> Is it appropriate to use ANY() in a select statement as so?
>
> SELECT * FROM table t WHERE t.id = ANY(ARRAY[1,2,3]);

Yes, that's appropriate. A simpler formulation (for that simple case)
is:

    ... WHERE t.id IN (1,2,3)

> A less trivial usage of the above would be to pass an array to a simple
> function using it to return a range of arbitrary rows.

I don't know exactly what you mean by that.

>  The alternative to
> this would be to (programmatically) call the function multiple times on a
> list of arguments. Some questions:

Generally you don't want to submit multiple queries to answer one
question.

> 1) How does ANY() behave on indexed columns?

It can use an index.

> 2) How does ANY() behave when passed an array with one element?

The same as when passed multiple elements. The planner treats it
differently than just doing "t.id = 1", but it can still use an index.
It appears more likely to use a bitmap index scan plan, and maybe it
can't use a normal index scan in that situation.

> 3) Generally is it better to use ANY on a passed ARRAY, or to just call a
> select multiple times (and aggregate the results)? Is ANY just a glorified
> OR?

Using ANY or IN is generally better. The planner is able to do the index
scan in one pass using ANY or IN; if you use a chain of ORs it does
multiple bitmap scans and ORs the results together.

You should try experimenting a little to find the answers to questions
like this. EXPLAIN and EXPLAIN ANALYZE can tell you a lot.

Regards,
    Jeff Davis

--------------------------------------------------
From: "Jeff Davis" <pgsql@j-davis.com>

> On Sun, 2009-06-07 at 19:33 +0100, Shakil Shaikh wrote:
>
>> A less trivial usage of the above would be to pass an array to a simple
>> function using it to return a range of arbitrary rows.
>
> I don't know exactly what you mean by that.

I don't think that you can pass an operand of IN to a stored function, while
you can ARRAYs. This would let you select arbitrarily chosen rows
(identified by id, say) via a single ARRAY parameter, in a "variable
parameter list" kind of way.

>
>> 3) Generally is it better to use ANY on a passed ARRAY, or to just call a
>> select multiple times (and aggregate the results)? Is ANY just a
>> glorified
>> OR?
>
> Using ANY or IN is generally better. The planner is able to do the index
> scan in one pass using ANY or IN; if you use a chain of ORs it does
> multiple bitmap scans and ORs the results together.
>
> You should try experimenting a little to find the answers to questions
> like this. EXPLAIN and EXPLAIN ANALYZE can tell you a lot.
>

I have since done this. Using a simple table of 5m rows, it appears that the
single element case runs just as fast as using a direct select which is
nice. As you suggest, ANY also runs faster than using the equivalent OR, and
quite a bit faster than multiple selects per ARRAY item.

I don't see many drawbacks with using this method!

Shak