X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 5B5E83A55BD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 22:23:23 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 85198-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Dec 2004 22:23:13 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id AEF853A555E
	for <pgsql-sql@postgresql.org>; Wed,  1 Dec 2004 22:23:14 +0000 (GMT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 5BA2535689; Wed,  1 Dec 2004 14:23:12 -0800 (PST)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 5A36835678; Wed,  1 Dec 2004 14:23:12 -0800 (PST)
Date: Wed, 1 Dec 2004 14:23:12 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Sandro Joel Eller <sandro@tecsoft.com.br>
Cc: PGSQL <pgsql-sql@postgresql.org>
Subject: Re: Query is slower
In-Reply-To: <41AE1B03.50205@tecsoft.com.br>
Message-ID: <20041201142001.M66421@megazone.bigpanda.com>
References: <41AE1B03.50205@tecsoft.com.br>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200412/9
X-Sequence-Number: 19629


On Wed, 1 Dec 2004, Sandro Joel Eller wrote:

> I have a query using "like" operator (select * from name like 'JOHN%'),
> but the table has about 500 hundred records. The  has a index (create
> index ixcontract_name on contract (name)) , but it is very slow because
> it is not using index. How do I do  the query to use index?

Well, there are two possibilities.  The more likely one is that the
database is not in "C" locale so it won't use the index described (it
would need an index with a <type>_pattern_ops operator class).  If this is
the case, the two options are making the pattern_ops index or re-initdb
with "C" locale.

The other, less likely, option is that it's expecting a large percentage
of rows to match which you can check with EXPLAIN.