|
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. |
