Skip site navigation (1) Skip section navigation (2)

Re: Query Performance and IOWait

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: Andrew Janian <ajanian(at)scottrade(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query Performance and IOWait
Date: 2004-11-18 16:44:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

What is your statistics target?
What is your effective_cache_size?

Have you tried running the query as a cursor?


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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Thursday, November 18, 2004 9:40 AM
> To: Andrew Janian
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Query Performance and IOWait 
> "Andrew Janian" <ajanian(at)scottrade(dot)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-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).
> 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?

Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd(at)commandprompt(dot)com -
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment: jd.vcf
Description: text/x-vcard (640 bytes)

In response to

pgsql-performance by date

Next:From: Dave CramerDate: 2004-11-18 17:09:08
Subject: Interaction between Free Space Map an alternate location for a database
Previous:From: Tom LaneDate: 2004-11-18 16:43:12
Subject: Timing of pgstats updates

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group