Date: Wed, 1 Dec 2004 13:31:12 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Sandro Joel Eller <sandro@tecsoft.com.br>
Cc: PGSQL <pgsql-sql@postgresql.org>
Subject: Re: Query is slower
Message-ID: <20041201203112.GA25240@winnie.fuhr.org>
References: <41AE1B03.50205@tecsoft.com.br>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <41AE1B03.50205@tecsoft.com.br>

On Wed, Dec 01, 2004 at 05:26:59PM -0200, 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?

How do you know the query doesn't use an index and that using an
index would be faster?  Did you run EXPLAIN ANALYZE?  If so, then
please post the output.  It might also be useful to see the difference
between a sequential scan and an index scan.  Try this:

EXPLAIN ANALYZE SELECT * FROM contract WHERE name LIKE 'JOHN%';
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM contract WHERE name LIKE 'JOHN%';

If the second query still does a sequential scan then you might
have a data type mismatch.  Or, if the search string begins with
% or _, then the query won't be able to use an index.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/