Re: Very slow query

On Tue, 2002-07-30 at 16:03, Josh Berkus wrote:
> 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.

A couple of months ago, I asked a similar question, when I saw
that that a COUNT(*) was scanning, even though it had a perfect
supporting index. This is the paraphrased answer:
The aggregate functions module is a complicated black box that
the developers are scared to look at and break. Besides, I
hardly ever use COUNT(*), so you don't need it either.

Horrified by the fact that aggregate don't use indexes, I've
stopped my experiments with Postgres, since reporting/summarizing
is what we were looking to Postgres for, anyway.

--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "The greatest dangers to liberty lurk in insidious encroachment |
| by men of zeal, well-meaning, but without understanding." |
| Justice Louis Brandeis, dissenting, Olmstead v US (1928) |
+-----------------------------------------------------------------+