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,
> -----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 - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Description: text/x-vcard (640 bytes)
In response to
pgsql-performance by date
|Next:||From: Dave Cramer||Date: 2004-11-18 17:09:08|
|Subject: Interaction between Free Space Map an alternate location for a database|
|Previous:||From: Tom Lane||Date: 2004-11-18 16:43:12|
|Subject: Timing of pgstats updates|