From: | "Andrew Janian" <ajanian(at)scottrade(dot)com> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Query Performance and IOWait |
Date: | 2004-11-18 13:42:20 |
Message-ID: | CE10B12929C8584090A3A10DCA0DAE71137E7BF3@exchstl1.scottrade.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello All,
I have a setup with a Dell Poweredge 2650 with Red Hat and Postgres 7.4.5 with a database with about 27GB of data. The table in question has about 35 million rows.
I am running the following query:
SELECT *
FROM mb_fix_message
WHERE msg_client_order_id IN (
SELECT msg_client_order_id
FROM mb_fix_message
WHERE msg_log_time >= '2004-06-01'
AND msg_log_time < '2004-06-01 13:30:00.000'
AND msg_message_type IN ('D','G')
AND mb_ord_type = '1'
)
AND msg_log_time > '2004-06-01'
AND msg_log_time < '2004-06-01 23:59:59.999'
AND msg_message_type = '8'
AND (mb_raw_text LIKE '%39=1%' OR mb_raw_text LIKE '%39=2%');
with the following plan:
QUERY PLAN
Nested Loop IN Join (cost=0.00..34047.29 rows=1 width=526)
-> Index Scan using mfi_log_time on mb_fix_message (cost=0.00..22231.31 rows=2539 width=526)
Index Cond: ((msg_log_time > '2004-06-01 00:00:00'::timestamp without time zone) AND (msg_log_time < '2004-06-01 23:59:59.999'::timestamp without time zone))
Filter: (((msg_message_type)::text = '8'::text) AND (((mb_raw_text)::text ~~ '%39=1%'::text) OR ((mb_raw_text)::text ~~ '%39=2%'::text)))
-> Index Scan using mfi_client_ordid on mb_fix_message (cost=0.00..445.56 rows=1 width=18)
Index Cond: (("outer".msg_client_order_id)::text = (mb_fix_message.msg_client_order_id)::text)
Filter: ((msg_log_time >= '2004-06-01 00:00:00'::timestamp without time zone) AND (msg_log_time < '2004-06-01 13:30:00'::timestamp without time zone) AND ((msg_message_type)::text = 'D'::text) OR ((msg_message_type)::text = 'G'::text)) AND ((mb_ord_type)::text = '1'::text))
While running, this query produces 100% iowait usage on its processor and takes a ungodly amount of time (about an hour).
The postgres settings are as follows:
shared_buffers = 32768 # min 16, at least max_connections*2, 8KB each
sort_mem = 262144 # min 64, size in KB
And the /etc/sysctl.conf has:
kernel.shmall = 274235392
kernel.shmmax = 274235392
The system has 4GB of RAM.
I am pretty sure of these settings, but only from my reading of the docs and others' recommendations online.
Thanks,
Andrew Janian
OMS Development
Scottrade Financial Services
(314) 965-1555 x 1513
Cell: (314) 369-2083
From | Date | Subject | |
---|---|---|---|
Next Message | Richard_D_Levine | 2004-11-18 13:56:41 | Re: Query Performance and IOWait |
Previous Message | Leeuw van der, Tim | 2004-11-18 10:53:08 | Re: Tsearch2 really slower than ilike ? |