Re: my query is so slow,It costs 944,319.072 ms,Can someone Help!! - Mailing list pgsql-admin

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