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