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 

I am running the following query:

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


Andrew Janian
OMS Development
Scottrade Financial Services
(314) 965-1555 x 1513
Cell: (314) 369-2083

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to