Sam Mason wrote:
On Thu, Nov 19, 2009 at 05:24:33PM +0100, Pavel Stehule wrote:
it should be little bit more effective:
I'm not sure if it will be much more; when you put a set returning
function into a FROM clause PG will always run the function to
completion---as far as I know, but I've only got 8.3 for testing at the
moment. I'm also not sure why you want to return zero when you don't
find the element. The code also exploits an implementation artifact of
PG that the zero (i.e. the RHS of your UNION ALL) will be "after" the
real index.
This raises a small and interesting optimization for PG, when it does
the plan it could notice that a UNION ALL followed by a LIMIT won't need
to return all rows and hence it may be better to run the "quicker" one
first. Or would this end up breaking more code than it helps?
CREATE OR REPLACE FUNCTION idx(anyarray, anyelement)
RETURNS int AS $$
SELECT i
FROM generate_series(array_lover($1,1),array_upper($1,1)) g(i)
Quality typo :) ^^^
WHERE $1[i] = $2
UNION ALL
SELECT 0 -- return 0 as not found
LIMIT 1; -- stop after first match
$$ LANGUAGE sql;
I'd do something like:
CREATE OR REPLACE FUNCTION firstidx(anyarray, anyelement)
RETURNS int AS $$
SELECT i FROM (
SELECT generate_series(array_lower($1,1),array_upper($1,1))) g(i)
WHERE $1[i] = $2
LIMIT 1;
$$ LANGUAGE sql IMMUTABLE;
You can replace the call to array_upper with some large number to check
either function's behavior with large arrays.
I agree that it should return null when the item is not found. So I
tested both and Sam is correct. His function performs the same whether
there are 500 elements or 50,000.
We had an idx() function in the _int contrib module. I wonder if it
would be useful to write this in C now that _int is deprecated?
Scott
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
