Re: Postgresql is very slow

2008/6/24 Scott Marlowe <scott.marlowe@gmail.com>:
> On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <bijayant4u@yahoo.com> wrote:
(...)
>> The output of EXPLAIN query;
>>
>> select * from USERS where email like '%bijayant.kumar%';
>> This simplest query tooks 10 minutes and server loads goes from 0.35 to 16.94.
>>
>> EXPLAIN select * from USERS where email like '%bijayant.kumar%';
>>                          QUERY PLAN
>> --------------------------------------------------------------
>>  Seq Scan on USERS  (cost=0.00..54091.84 rows=1 width=161)
>>   Filter: ((email)::text ~~ '%bijayant.kumar%'::text)
>> (2 rows)
>
> You're scanning ~ 54094 sequential pages to retrieve 1 row.   Note
> that explain analyze is generally a better choice, it gives more data
> useful for troubleshooting.
>
> Definitely need a vacuum full on this table, likely followed by a reindex.

This is a LIKE query with a wildcard at the start of the string to
match, reindexing won't help much.


Ian Barwick