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(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?
>
> 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(at)commandprompt(dot)com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL