Below is the EXPLAIN ANALYZE output of a typical current query.
I have just begun looking at tsearch2 to index the header and body
fields.
I have also been using 'atop' to see I/O stats on the disk, i am now
pretty sure thats where the current bottleneck is. As soon as a query
is launched the IO goes up to 100% on sdh while the CPU sits at <40%.
EXPLAIN ANALYZE SELECT meta.msg_id, meta.date, meta.subject FROM
message, meta WHERE meta.date >= '2002-07-05 00:00:00' AND meta.date <=
'2002-08-05 00:00:00' AND message.body||message.header ILIKE '%chicken%'
AND meta.sys_id = message.sys_id ORDER BY col_date DESC;
QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-----------
Nested Loop (cost=0.00..320901.89 rows=440 width=150) (actual time=
1558.44..344597.66 rows=2512 loops=1)
-> Index Scan Backward using meta_col_date_index on meta (cost=0.00..
54163.01 rows=88004 width=142) (actual time=29.17..46317.81 rows=149520
loops=1)
Index Cond: ((date >= '2002-07-05 00:00:00'::timestamp without time
zone) AND (date <= '2002-08-05 00:00:00'::timestamp without time zone))
-> Index Scan using nntp_message_pkey on nntp_message (cost=0.00..3.
02 rows=1 width=8) (actual time=1.99..1.99 rows=0 loops=149520)
Index Cond: ("outer".sys_id = message.sys_id)
Filter: ((body || header) ~~* '%chicken%'::text)
Total runtime: 344612.85 msec
(7 rows)
Thanks!
--
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
