LIKE Query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux