Re: Using ANY()

From: "Shakil Shaikh" <sshaikh(at)hotmail(dot)com>
To: "Jeff Davis" <pgsql(at)j-davis(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Using ANY()
Date: 2009-06-08 18:18:28
Message-ID: BAY117-DS954D3FA448716DA75D714AC470@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

--------------------------------------------------
From: "Jeff Davis" <pgsql(at)j-davis(dot)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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ioana Danes 2009-06-08 18:25:45 Re: Duplicate key issue in a transaction block
Previous Message Gerhard Wiesinger 2009-06-08 18:09:14 Re: Sum of multiplied deltas