X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DA271D1B478 for ; Fri, 19 Dec 2003 03:09:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 21276-05 for ; Thu, 18 Dec 2003 23:08:43 -0400 (AST) Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 2785DD1B47D for ; Thu, 18 Dec 2003 23:08:39 -0400 (AST) Received: (qmail 32257 invoked from network); 19 Dec 2003 03:08:37 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.36?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 19 Dec 2003 03:08:37 -0000 Subject: Re: is it possible to get the optimizer to use indexes From: Dave Cramer Reply-To: pg@fastcrypt.com To: Christopher Kings-Lynne Cc: pgsql-performance@postgresql.org In-Reply-To: <3FE256AA.8000707@familyhealth.com.au> References: <3FE1F5A1.6080908@geizhals.at> <60n09q9bqg.fsf@dev6.int.libertyrms.info> <1071789528.1629.10.camel@localhost.localdomain> <3FE256AA.8000707@familyhealth.com.au> Content-Type: text/plain Organization: Cramer Consulting Message-Id: <1071803317.1629.16.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 18 Dec 2003 22:08:37 -0500 Content-Transfer-Encoding: 7bit X-AntiVirus: scanned for viruses by AMaViS 0.2.1 (http://amavis.org/) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/269 X-Sequence-Number: 5129 after vacuum verbose analyze, I still get explain select * from isppm where item_upc_cd like '06038301234'; QUERY PLAN ----------------------------------------------------------------------- Seq Scan on isppm (cost=100000000.00..100009684.89 rows=2 width=791) Filter: (item_upc_cd ~~ '06038301234'::text) (2 rows) isp=# explain select * from isppm where item_upc_cd = '06038301234'; QUERY PLAN ------------------------------------------------------------------------ Index Scan using isppm_x0 on isppm (cost=0.00..5.86 rows=2 width=791) Index Cond: (item_upc_cd = '06038301234'::bpchar) (2 rows) Dave On Thu, 2003-12-18 at 20:38, Christopher Kings-Lynne wrote: > > It appears that the optimizer only uses indexes for = clause? > > The optimizer will used indexes for LIKE clauses, so long as the clause > is a prefix search, eg: > > SELECT * FROM test WHERE a LIKE 'prf%'; > > Chris > >