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!!
Date: Fri, 04 Jan 2013 10:58:14 -0500
Message-ID: <19679.1357315094@sss.pgh.pa.us> (raw)
In-Reply-To: <50E67019.6060908@countrybright.com.cn>
References: <50E66B33.3070707@countrybright.com.cn>
<50E67019.6060908@countrybright.com.cn>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, iori@countrybright.com.cn
Subject: Re: my query is so slow,It costs 944,319.072 ms,Can someone Help!!
In-Reply-To: <19679.1357315094@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox