We won't need full text searching capabilities as in documents as the data type is varchar. Wildspeed will exactly fit our needs. -----Original Message----- From: Oleg Bartunov [mailto:oleg@xxxxxxxxxx] Sent: Wednesday, January 28, 2009 1:27 AM To: Hari, Balaji Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: LIKE Query performance Only wildspeed http://www.sai.msu.su/~megera/wiki/wildspeed has index support for %text% But, it has limitations. Oleg On Tue, 27 Jan 2009, Hari, Balaji wrote: > Hi, > > I am relatively new to PostgreSQL(8.1) and facing the following problem. > > We have indexes defined on timestamp and description (create index description_idx on event using btree (description varchar_pattern_ops)) > > EXPLAIN ANALYZE SELECT event_id, category, current_session_number, description, event_type_id, realm_name, root_session_number, severity, source_name, target_key, target_name, timestamp, jdo_version FROM event WHERE description like '%mismatch%' ORDER BY timestamp desc; > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------- > Sort (cost=36267.09..36272.73 rows=2256 width=314) (actual time=19255.075..20345.774 rows=647537 loops=1) > Sort Key: "timestamp" > Sort Method: external merge Disk: 194080kB > -> Seq Scan on event (cost=0.00..36141.44 rows=2256 width=314) (actual time=0.080..1475.041 rows=647537 loops=1) > Filter: ((description)::text ~~ '%mismatch%'::text) > Total runtime: 22547.292 ms > (6 rows) > > But startsWith query use indexes. > > EXPLAIN ANALYZE SELECT event_id, category, current_session_number, description, event_type_id, realm_name, root_session_number, severity, source_name, target_key, target_name, timestamp, jdo_version FROM event WHERE description like 'mismatch%' ORDER BY timestamp desc; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------- > Sort (cost=9.26..9.27 rows=1 width=314) (actual time=0.766..0.766 rows=0 loops=1) > Sort Key: "timestamp" > Sort Method: quicksort Memory: 17kB > -> Index Scan using description_idx on event (cost=0.00..9.25 rows=1 width=314) (actual time=0.741..0.741 rows=0 loops=1) > Index Cond: (((description)::text ~>=~ 'mismatch'::text) AND ((description)::text ~<~ 'mismatci'::text)) > Filter: ((description)::text ~~ 'mismatch%'::text) > Total runtime: 0.919 ms > (7 rows) > > Is there any tweaks to force pgsql to use index on description? > > Balaji > > P.S The event database has 700k records. > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
