Re: Query Performance and IOWait

Hello,

What is your statistics target?
What is your effective_cache_size?

Have you tried running the query as a cursor?

Sincerely,

Joshua D. Drake



Andrew Janian wrote:
> I have run ANALYZE right before running this query.
>
> I will run EXPLAIN ANALYZE when I can.  I started running the query when I sent the first email and it is still
running. Looke like it longer than an hour. 
>
> I will post the results of EXPLAIN ANALYZE in a few hours when I get them.
>
> Thanks for all your help,
>
> Andrew
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, November 18, 2004 9:40 AM
> To: Andrew Janian
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Query Performance and IOWait
>
>
> "Andrew Janian" <ajanian@scottrade.com> writes:
>
>>

                                 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-0123: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'::timestampwithout 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).
>
>
> This plan looks fairly reasonable if the rowcount estimates are
> accurate.  Have you ANALYZEd the table lately?  You might need to
> bump up the statistics target for the msg_log_time column to improve
> the quality of the estimates.  It would be useful to see EXPLAIN
> ANALYZE results too (yes I know it'll take you an hour to get them...)
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL