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