Re: intercepting where clause on a view or other performance tweak

I should've also mentioned that we're using PG 9.0.



-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Russell Keane
Sent: 16 November 2012 15:18
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak

Sorry, I should've added that in the original description.
I have an index on search_key and it's never used.

If it makes any difference, the table is about 9MB and the index on that field alone is 3MB.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 16 November 2012 15:05
To: Russell Keane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak

Russell Keane <Russell.Keane@inps.co.uk> writes:
> Running the following query takes 56+ ms as it does a seq scan of the whole table:
> SELECT CODE FROM stuff
>    WHERE SEARCH_KEY LIKE 'AAAAAA%'

Why don't you create an index on search_key, and forget all these other machinations?  (If your locale isn't C you'll
needto use a varchar_pattern_ops index.) 

            regards, tom lane


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance