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
Date: Wed, 1 Dec 2004 13:31:12 -0700
Message-ID: <20041201203112.GA25240@winnie.fuhr.org> (raw)
In-Reply-To: <41AE1B03.50205@tecsoft.com.br>
References: <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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, sandro@tecsoft.com.br
Subject: Re: Query is slower
In-Reply-To: <20041201203112.GA25240@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox