Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZNTq-00045g-Ct for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 15:05:18 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TZNTk-0003Eu-6k for pgsql-performance@postgresql.org; Fri, 16 Nov 2012 15:05:18 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id qAGF58bA014369; Fri, 16 Nov 2012 10:05:08 -0500 (EST) From: Tom Lane To: Russell Keane cc: "pgsql-performance@postgresql.org" Subject: Re: intercepting where clause on a view or other performance tweak In-reply-to: <8D0E5D045E36124A8F1DDDB463D548557D0CDC68A8@mxsvr1.is.inps.co.uk> References: <8D0E5D045E36124A8F1DDDB463D548557D0CDC68A8@mxsvr1.is.inps.co.uk> Comments: In-reply-to Russell Keane message dated "Fri, 16 Nov 2012 14:28:25 +0000" Date: Fri, 16 Nov 2012 10:05:07 -0500 Message-ID: <14368.1353078307@sss.pgh.pa.us> X-Pg-Spam-Score: -2.0 (--) X-Archive-Number: 201211/185 X-Sequence-Number: 48551 Russell Keane 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