Edmund Dengler wrote:
> This just returns us to the problem that the use of a function causes a
> sequential scan (as the select has to essentially apply the function to
> each row). I would need to store a dummy value into the field (it is an
> int, so I could store -1, but it breaks my sense of aesthetics to do this
> simply to get around the sequential scan).
>
> Could I use a functional index, maybe?
Yes, but I think you have to write a little wrapper:
CREATE TABLE foo (
key integer not null,
value text);
CREATE FUNCTION toValue(text) RETURNS text AS '
SELECT COALESCE($1, '''');
' LANGUAGE 'SQL' IMMUTABLE;
CREATE INDEX i_foo1 ON foo(toValue(value));
And always be sure to use the function in the query:
SELECT *
FROM foo
WHERE toValue(value) = '';
For fun:
SET enable_seqscan to off;
EXPLAIN SELECT * FROM foo WHERE toValue(value) = 'Mike';
should produce an Index Scan....
HTH,
Mike Mascari
mascarm@mascari.com
