Chad,
> Its the substring function that slows things down so much. Is there a
better way to compare these string values?
> substr(cr.phonenum, 1,6) is the same speed. (and according to the docs,
basicly the same function)
Yeah. You can index it:
CREATE FUNCTION phonenum_substr (
VARCHAR ) RETURNS VARCHAR AS '
SELECT substr(cr.phonenum, 1, 6);
' LANGUAGE 'sql' WITH (ISCACHABLE, ISSTRICT);
-- this lets you index on the substring, as the straight substr function is
not indexable as of Postgres 7.2.1
Then:
CREATE INDEX idx_cr_phonenum_substr ON phonenum_substr(cr.phonenum);
This should speed things up.
--
-Josh Berkus
Aglio Database Solutions
San Francisco