Обсуждение: 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