From: | "Hari, Balaji" <Balaji_Hari(at)bmc(dot)com> |
---|---|
To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | LIKE Query performance |
Date: | 2009-01-27 23:41:50 |
Message-ID: | 425C04840926804AB81C297EDDE7B91717774F947E@HOUCCRPRD01.adprod.bmc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Oleg Bartunov | 2009-01-28 06:26:54 | Re: LIKE Query performance |
Previous Message | Josh Berkus | 2009-01-27 17:33:47 | Re: Odd behavior with temp usage logging |