From: | psql-mail(at)freeuk(dot)com |
---|---|
To: | shridhar_daithankar(at)persistent(dot)co(dot)in |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | # Re: Monthly table partitioning for fast purges? |
Date: | 2003-08-05 11:26:45 |
Message-ID: | E19jzxl-0008LL-00@buckaroo.freeuk.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
--
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Partyka | 2003-08-05 11:59:02 | Re: How to do? |
Previous Message | Knut P. Lehre | 2003-08-05 11:23:57 | Hardware Performance Tuning |