Re: LIKE Query performance - Mailing list pgsql-performance

From Oleg Bartunov
Subject Re: LIKE Query performance
Date
Msg-id Pine.LNX.4.64.0901292205510.9554@sn.sai.msu.ru
Whole thread Raw
In response to Re: LIKE Query performance  ("Hari, Balaji" <Balaji_Hari@bmc.com>)
List 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 by date:

Previous
From: "Hari, Balaji"
Date:
Subject: Re: LIKE Query performance
Next
From: Robert Haas
Date:
Subject: Re: Max on union