--------------------------------------------------
From: "Jeff Davis" <pg...@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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
