MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8;
 format=flowed
Content-Transfer-Encoding: 7bit
Date: Fri, 04 Jan 2013 08:36:41 +0000
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!!
In-Reply-To: <50E67019.6060908@countrybright.com.cn>
References: <50E66B33.3070707@countrybright.com.cn>
	<50E67019.6060908@countrybright.com.cn>
Message-ID: <c44e20e92fbfa6aa5eb6292ec84c87d7@edesix.com>
User-Agent: Roundcube Webmail/0.7.2
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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