From: | JB <jimbag(at)kw(dot)igs(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SELECT speed with LIKE |
Date: | 2000-04-01 18:33:44 |
Message-ID: | 38E64108.36606AE9@kw.igs.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've got a real problem with the speed of a select. Some folk might
recall a prev post about a month ago about this 50MB table taking 20+
seconds to complete a query. This table has about 70,000 records in it.
The table has a field called 'stname char(17)' which is indexed. The
query is "SELECT * FROM data WHERE stname LIKE 'MAIN%'". I'm running on
Redhat 6.1, 128MB ram, 40GB, P350. The actual index file is 4 MB.
EXPLAIN tells that it will use the index. The thing still takes about 20
seconds to complete. Can anyone make some suggestions on how I can speed
this up? It seems like such a simple problem I can't see the solution.
Any help would be most appreciated as this abosolutly has to be fixed
and I don't want to have to convert to another DB.
BTW I've run the same thing on a similar machine with 64 MB ram and it
take over a minute to complete. EXPLAIN says...
NOTICE: QUERY PLAN:
Index Scan using nx_data2 on data (cost=3352.28 rows=1 width=440)
EXPLAIN
jim
--
Get hold of portable property. -- Charles Dickens, "Great Expectations"
From | Date | Subject | |
---|---|---|---|
Next Message | Nilesh A. Phadke | 2000-04-01 20:49:09 | Embedded SQL -- ecpg |
Previous Message | Thomas Lockhart | 2000-04-01 06:12:05 | Re: [INTERFACES] Problem with Large Object Interface ofpgsql_perl5 |