Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group