pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery is slower
3+ messages / 3 participants
[nested] [flat]
* Query is slower
@ 2004-12-01 19:26 Sandro Joel Eller <sandro@tecsoft.com.br>
0 siblings, 2 replies; 3+ messages in thread
From: Sandro Joel Eller @ 2004-12-01 19:26 UTC (permalink / raw)
To: pgsql-sql
Hi
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?
Sandro
--
*Sandro Joel Eller*
Analista/Programador Senior
www.tecsoft.com.br
sandro@tecsoft.com.br
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query is slower
@ 2004-12-01 20:31 Michael Fuhr <mike@fuhr.org>
parent: Sandro Joel Eller <sandro@tecsoft.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2004-12-01 20:31 UTC (permalink / raw)
To: Sandro Joel Eller <sandro@tecsoft.com.br>; +Cc: 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/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Query is slower
@ 2004-12-01 22:23 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Sandro Joel Eller <sandro@tecsoft.com.br>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2004-12-01 22:23 UTC (permalink / raw)
To: Sandro Joel Eller <sandro@tecsoft.com.br>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-01 22:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-01 19:26 Query is slower Sandro Joel Eller <sandro@tecsoft.com.br>
2004-12-01 20:31 ` Michael Fuhr <mike@fuhr.org>
2004-12-01 22:23 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
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