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(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Russell Keane
Sent: 16 November 2012 15:18
To: pgsql-performance(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 16 November 2012 15:05
To: Russell Keane
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] intercepting where clause on a view or other performance tweak

Russell Keane <Russell(dot)Keane(at)inps(dot)co(dot)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 need to use a varchar_pattern_ops index.)

regards, tom lane