From: robin <robin@edesix.com>
To: 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 08:36:41 +0000
Message-ID: <c44e20e92fbfa6aa5eb6292ec84c87d7@edesix.com> (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>
I think I am correct in saying that the first (inner most) join is the
problem.
All the actual time is spent running this nested loop.
It does a single sequential scan of the m_transaction table (takes
about 1 second) filtering out all the m_transaction records that match
your criteria (28936 records).
For each of these records it then does a sequential scan of the
ap_attribute table looking for records with isactive='Y'.
For each such record it joins it to the m_transaction record if the
m_product_id in the m_transaction record matches the m_product_id in the
ap_attribute record.
That produces 28860 records and takes 938 seconds.
I suspect that if you had an index on
ap_attribute.m_product_id,ap_attribute.isactive then it might go a lot
faster (depending on the actual size of the ap_attribute table).
Note that I am not 100% sure that the query planner will immediately
use the index after you've created it - you might have to run analyze on
the ap_attribute table first.
Hope this helps,
Robin
--
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: robin@edesix.com
Subject: Re: my query is so slow,It costs 944,319.072 ms,Can someone Help!!
In-Reply-To: <c44e20e92fbfa6aa5eb6292ec84c87d7@edesix.com>
* 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