From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jerome <iori@countrybright.com.cn>
cc: pgsql-admin@postgresql.org
Subject: Re: my query is so slow,It costs 944,319.072 ms,Can someone Help!!
In-reply-to: <50E67019.6060908@countrybright.com.cn>
References: <50E66B33.3070707@countrybright.com.cn>
	<50E67019.6060908@countrybright.com.cn>
Comments: In-reply-to Jerome <iori@countrybright.com.cn>
	message dated "Fri, 04 Jan 2013 14:00:57 +0800"
Date: Fri, 04 Jan 2013 10:58:14 -0500
Message-ID: <19679.1357315094@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Jerome <iori@countrybright.com.cn> writes:
> I have a postgresql 9.1 in my server,And here 's my query,It costs 
> 944,319.072 ms,it is so slow,query result have 27  records.

It looks to me like your problem is here:

>                                              ->  Seq Scan on 
> m_transaction  (cost=0.00..188132.06 rows=1 width=78) (actual 
> time=0.015..932.400 rows=28936 loops=1)
>                                                    Filter: 
> ((m_inoutline_id IS NOT NULL) AND ((ad_client_id)::text = ANY 
> ('{0,FF80818129E99DC80129E9AD546A0708}'::text[])) AND (movementdate >= 
> '2012-12-01 00:00:00'::timestamp without time zone) AND (movementdate < 
> '2013-01-01 00:00:00'::timestamp without time zone) AND 
> ((ad_org_id)::text = ANY 
> ('{44523886B0E0423584BB0E87133F3EE4,4510F92E007E48B28EE6CEB088A78348,0}'::text[])))

If that rowcount estimate weren't so badly off (1 vs 28936), the planner
would have picked a more appropriate join method.  So you need to look
into why it's misestimating the effect of those filter conditions so
much.  First question of course is whether the table has been analyzed
lately.  If ANALYZE fixes it then it would seem auto-analyze is asleep
at the switch --- you didn't turn that off did you?

			regards, tom lane


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin