2008/6/24 Scott Marlowe <[EMAIL PROTECTED]>: > On Mon, Jun 23, 2008 at 11:48 PM, bijayant kumar <[EMAIL PROTECTED]> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
