Обсуждение: Multiple-index optimization not working for = ANY operator
Hello, From Section 11.4 of the Postgres 8.1 documentation, a new optimization is shipped in the latest release. "... a query like WHERE x = 42 OR x = 47 OR x = 53 OR x = 99 could be broken down into four separate scans of an index on x, each scan using one of the query clauses. The results of these scans are then ORed together to produce the result." While the feature works fine for cases such as: SELECT * FROM foo WHERE id IN (1, 2); And SELECT * FROM foo WHERE id = 1 OR id = 2; I find that it doesn't work (i.e. index is not used and a sequential scan is needed), if I have the following instead: SELECT * FROM foo WHERE id = ANY (ARRAY[1,2]); Is this expected? The reason I would like the last case to work is that my plpgsql function takes as input an array of IDs, and so I cannot write my query using the first two forms above. Any idea on how I can get around this is greatly appreciated. Thanks Jimmy
"Jimmy Choi" <JCHOI@altera.com> writes: > I find that it doesn't work (i.e. index is not used and a sequential > scan is needed), if I have the following instead: > SELECT * FROM foo WHERE id = ANY (ARRAY[1,2]); > Is this expected? Yes. That construct isn't indexable, period, in any existing release (it does work in CVS tip though...) regards, tom lane
On 2006-02-15, "Jimmy Choi" <JCHOI@altera.com> wrote: > I find that it doesn't work (i.e. index is not used and a sequential > scan is needed), if I have the following instead: > > SELECT * FROM foo WHERE id = ANY (ARRAY[1,2]); > > Is this expected? The reason I would like the last case to work is that > my plpgsql function takes as input an array of IDs, and so I cannot > write my query using the first two forms above. > > Any idea on how I can get around this is greatly appreciated. The workaround (for versions 7.4 - 8.1) is: select * from foo where id in (select myarray[i] from generate_series(array_lower(myarray,1), array_upper(myarray,1)) as s(i)); This typically plans out as a nestloop join with an aggregate over the function scan as the outer path, and an index lookup on foo.id as the inner path. Execution times for 8.1 are usually very slightly worse than the equivalent IN (a,b,c,...) with literal values, but the plan time is shorter. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services