Re: Query is slower

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: Sandro Joel Eller <sandro(at)tecsoft(dot)com(dot)br>
Cc: PGSQL <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Query is slower
Date: 2004-12-01 20:31:12
Message-ID: 20041201203112.GA25240@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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/

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Andrew Thorley 2004-12-01 21:58:11 Re: inserting values into types
Previous Message Tom Lane 2004-12-01 20:10:12 Re: Found Large Files.. what objects are they?