Skip site navigation (1) Skip section navigation (2)

Re: Very slow query

From: Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net>
To: PgSQL Novice ML <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Very slow query
Date: 2002-07-30 21:30:15
Message-ID: 1028064615.21027.16.camel@haggis (view raw or flat)
Thread:
Lists: pgsql-novice
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)      |
+-----------------------------------------------------------------+


In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-07-30 22:59:56
Subject: Re: Very slow query
Previous:From: Josh BerkusDate: 2002-07-30 21:03:11
Subject: Re: Very slow query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group