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.l.johnson@cox.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)      |
+-----------------------------------------------------------------+