Re: LIKE Query performance
От | Oleg Bartunov |
---|---|
Тема | Re: LIKE Query performance |
Дата | |
Msg-id | Pine.LNX.4.64.0901292205510.9554@sn.sai.msu.ru обсуждение исходный текст |
Ответ на | Re: LIKE Query performance ("Hari, Balaji" <Balaji_Hari@bmc.com>) |
Список | pgsql-performance |
On Thu, 29 Jan 2009, Hari, Balaji wrote: > Is there a patch to make Wildspeed work with postgresql version 8.3.1? unfortunately, no. > > P.S > My bad, the version number was incorrect in my previous mail. > -----Original Message----- > From: Oleg Bartunov [mailto:oleg@sai.msu.su] > Sent: Wednesday, January 28, 2009 1:27 AM > To: Hari, Balaji > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] 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@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > > Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
В списке pgsql-performance по дате отправления: