Re: Very slow query

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