Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id A77F337C250 for ; Mon, 6 Oct 2008 21:15:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27496-01 for ; Mon, 6 Oct 2008 21:15:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from ey-out-2122.google.com (ey-out-2122.google.com [74.125.78.24]) by postgresql.org (Postfix) with ESMTP id D51C937C0C7 for ; Mon, 6 Oct 2008 21:15:52 -0300 (ADT) Received: by ey-out-2122.google.com with SMTP id 6so1008811eyi.61 for ; Mon, 06 Oct 2008 17:15:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=VKPxxFyAeeK7SNypZt+7gH9B9bJPAenlwVbPFPxj4MI=; b=rGrP6eT9ubdUXJZ4KQo/UQDa83QFIunFpPJCQpxLpHn40RbTXXH7KpDmqwBkxcPmlU rI/QGkLc5vY/W2kNezhSvveTdckUM8D3iNKXHeDjd09mVICDtC8Epr9SobSzQsAZIaJN 4buqOyZGceifajBveLvHMu4ioFyytwCTjor9M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=nAArdq8XtBQdxbzITVd1W3QUfT3aOVzPwcDzxgjnw6vE7A4Lz38qubyVmnioOiSjPW 3yDuyqQLCvhNH4aQeYhY0R088P0QJLkZBYAIUYyszi+NqOo2uiqZ1a/Ke7aJrvbm45pT VQ466MUg9emG/07NVdBzbWLDkv3PTVqdL5RgU= Received: by 10.103.246.17 with SMTP id y17mr3145231mur.55.1223338550585; Mon, 06 Oct 2008 17:15:50 -0700 (PDT) Received: by 10.102.253.1 with HTTP; Mon, 6 Oct 2008 17:15:50 -0700 (PDT) Message-ID: Date: Mon, 6 Oct 2008 20:15:50 -0400 From: "Greg Caulton" To: "Kevin Grittner" , pgsql-performance@postgresql.org Subject: Re: cant get an index scan with a LIKE In-Reply-To: <48EA6015.EE98.0025.0@wicourts.gov> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <48EA6015.EE98.0025.0@wicourts.gov> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/31 X-Sequence-Number: 31612 That worked great - THANKS! CREATE INDEX sct_descriptions_k2 ON sct_descriptions USING btree (term_index varchar_pattern_ops); I noticed I had to keep the original index for the non-like operator but that is not a big deal CREATE INDEX sct_descriptions_k1 ON sct_descriptions USING btree (term_index ); thanks again Greg On Mon, Oct 6, 2008 at 7:59 PM, Kevin Grittner wrote: >>>> "Greg Caulton" wrote: > >> but I get a sequential scan when I do where term_index like >> >> select * from sct_descriptions where term_index like 'CHILLS AND > FEVER >> (FINDING)' > >> Is there anything else I can do? Settings below, this is PostgreSQL > 8.3 > >> "lc_collate";"English_United States.1252" >> "lc_ctype";"English_United States.1252" >> "lc_messages";"English_United States" >> "lc_monetary";"English_United States" >> "lc_numeric";"English_United States" >> "lc_time";"English_United States" > > This issue is discussed here: > > http://www.postgresql.org/docs/8.2/interactive/locale.html > > with a solution to your specific problem mentioned here: > > http://www.postgresql.org/docs/8.2/interactive/indexes-opclass.html > > You can create an index with the appropriate operator type to get LIKE > to work as you want. I hope this helps. > > -Kevin >