Skip site navigation (1) Skip section navigation (2)

Re: LIKE Query performance

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: "Hari, Balaji" <Balaji_Hari(at)bmc(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: LIKE Query performance
Date: 2009-01-28 06:26:54
Message-ID: Pine.LNX.4.64.0901280924480.9554@sn.sai.msu.ru (view raw or flat)
Thread:
Lists: pgsql-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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

In response to

Responses

pgsql-performance by date

Next:From: Rohan PethkarDate: 2009-01-28 09:14:55
Subject: Need help in setting up DBT2 for PostgreSQL
Previous:From: Hari, BalajiDate: 2009-01-27 23:41:50
Subject: LIKE Query performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group