I should've also mentioned that we're using PG 9.0. -----Original Message----- From: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Russell Keane Sent: 16 November 2012 15:18 To: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: 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@xxxxxxxxxxxxx] Sent: 16 November 2012 15:05 To: Russell Keane Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: intercepting where clause on a view or other performance tweak Russell Keane <Russell.Keane@xxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
