pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard_D_Levine@raytheon.com
To: Andrew Janian <ajanian@scottrade.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-performance-owner@postgresql.org
Subject: Re: Query Performance and IOWait
Date: Thu, 18 Nov 2004 08:56:41 -0500
Message-ID: <OFF0E814F5.ED2DF886-ON05256F50.004C36B0@ftw.us.ray.com> (raw)

Andrew,

It seems that you could combine the subquery's WHERE clause with the main
query's to produce a simpler query, i.e. one without a subquery.

Rick




                                                                                                                                                 
                      "Andrew Janian"                                                                                                            
                      <ajanian@scottrade.com>            To:       <pgsql-performance@postgresql.org>                                            
                      Sent by:                           cc:                                                                                     
                      pgsql-performance-owner@pos        Subject:  [PERFORM] Query Performance and IOWait                                        
                      tgresql.org                                                                                                                
                                                                                                                                                 
                                                                                                                                                 
                      11/18/2004 08:42 AM                                                                                                        
                                                                                                                                                 
                                                                                                                                                 




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

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






view thread (14+ messages)  latest in thread

Message-ID: <OFF0E814F5.ED2DF886-ON05256F50.004C36B0@ftw.us.ray.com>
Permalink:  ../OFF0E814F5.ED2DF886-ON05256F50.004C36B0@ftw.us.ray.com/
Also on:    postgresql.org/message-id/OFF0E814F5.ED2DF886-ON05256F50.004C36B0@ftw.us.ray.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: Richard_D_Levine@raytheon.com, ajanian@scottrade.com, pgsql-performance-owner@postgresql.org
  Subject: Re: Query Performance and IOWait
  In-Reply-To: <OFF0E814F5.ED2DF886-ON05256F50.004C36B0@ftw.us.ray.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox