my query is so slow,It costs 944,319.072 ms,Can someone Help!!

[Jerome]

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.

SELECT
AP_ATTRIBUTE.stylename,AP_BASIC.style,AP_ATTRIBUTE.color,AP_ATTRIBUTE.size,
AP_BASIC.ap_sizesequence_id as apSizesequenceId,
M_TRANSACTION.movementdate as MOVEMENTDATE,
SUM(M_TRANSACTION.MOVEMENTQTY) AS MOVEMENTQTY,
COALESCE(M_PRODUCTPRICE.pricelist,999999) as pricelist,
M_INOUT.M_INOUT_ID, M_INOUT.DOCUMENTNO AS
DOCUMENTNO,C_BPARTNER.VALUE AS BPNAME, '' AS M_MOVEMENTLINE_ID, '' AS
MOVEMENT, '' AS MOVEMENT_DESCRIPTION, '' AS M_INVENTORY_ID, '' AS
M_MOVEMENT_ID, '' AS M_PRODUCTIONPLAN_ID, '' AS PRODUCTION_PLAN
FROM M_TRANSACTION
left join AP_ATTRIBUTE ON
AP_ATTRIBUTE.M_PRODUCT_ID=M_TRANSACTION.M_PRODUCT_ID
left join AP_BASIC ON AP_BASIC.AP_BASIC_ID=AP_ATTRIBUTE.AP_BASIC_ID
left join M_PRODUCTPRICE on M_TRANSACTION.M_PRODUCT_ID =
M_PRODUCTPRICE.M_PRODUCT_ID AND
M_PRODUCTPRICE.m_pricelist_version_id='69CBAFD24AB24E3CB9B9A44E282D62EC', M_INOUTLINE,
M_INOUT, C_BPARTNER
WHERE M_TRANSACTION.M_INOUTLINE_ID =
M_INOUTLINE.M_INOUTLINE_ID AND M_INOUTLINE.M_INOUT_ID =
M_INOUT.M_INOUT_ID AND M_TRANSACTION.M_INOUTLINE_ID IS NOT
NULL AND M_INOUT.C_BPARTNER_ID=C_BPARTNER.C_BPARTNER_ID AND
M_TRANSACTION.AD_CLIENT_ID IN
('0','FF80818129E99DC80129E9AD546A0708') AND
M_TRANSACTION.AD_ORG_ID IN
('44523886B0E0423584BB0E87133F3EE4','4510F92E007E48B28EE6CEB088A78348','0')
AND AP_ATTRIBUTE.isActive = 'Y' AND 1=1 AND
M_TRANSACTION.movementdate >= to_date('2012-12-01') AND
M_TRANSACTION.movementdate < to_date('2013-01-01') AND
M_INOUT.M_WAREHOUSE_ID ='50657AE0F49A421A9F10154C8632A726' AND
M_INOUT.ISSOTRX='N'
GROUP BY
AP_ATTRIBUTE.stylename,AP_BASIC.style,AP_ATTRIBUTE.color,AP_ATTRIBUTE.size,
AP_BASIC.ap_sizesequence_id, M_TRANSACTION.movementdate,
M_PRODUCTPRICE.pricelist, M_INOUT.M_INOUT_ID,
M_INOUT.DOCUMENTNO,C_BPARTNER.VALUE
ORDER BY M_TRANSACTION.movementdate, M_INOUT.M_INOUT_ID,style,color,size


it have 2962657 rows in M_TRANSACTION. pk:m_transaction_id,
index,m_product_id
it have 106380 rows in AP_ATTRIBUTE. pk:AP_ATTRIBUTE_id, no index
it have 6128 rows in AP_BASIC. pk:AP_BASIC_id, no index
it have 223917 rows in M_PRODUCTPRICE.
index(m_pricelist_version_id,m_product_id)
it have 2000329 rows in M_INOUTLINE. pk:m_inoutline_id,
index,m_product_id,m_inout_id
it have 45919 rows in M_INOUT. pk:m_inout_id, index,m_inout_id

explain analyze reveals this:

GroupAggregate (cost=201073.74..201073.78 rows=1 width=138) (actual
time=944255.849..944256.284 rows=517 loops=1)

-> Sort (cost=201073.74..201073.74 rows=1 width=138) (actual
time=944255.828..944255.859 rows=517 loops=1)

Sort Key: m_transaction.movementdate, m_inout.m_inout_id,
ap_basic.style, ap_attribute.color, ap_attribute.size,
ap_attribute.stylename, ap_basic.ap_sizesequence_id,
m_productprice.pricelist, m_inout.documentno, c_bpartner.value

Sort Method: quicksort Memory: 162kB

-> Nested Loop (cost=0.00..201073.73 rows=1 width=138)
(actual time=272067.456..944244.793 rows=517 loops=1)

-> Nested Loop (cost=0.00..201066.14 rows=1 width=146)
(actual time=272067.433..944239.495 rows=517 loops=1)

-> Nested Loop (cost=0.00..201058.34 rows=1
width=107) (actual time=13.225..943784.425 rows=28860 loops=1)

-> Nested Loop Left Join
(cost=0.00..201049.06 rows=1 width=107) (actual time=13.197..940249.631
rows=28860 loops=1)

-> Nested Loop Left Join
(cost=0.00..201040.64 rows=1 width=136) (actual time=13.122..938969.396
rows=28860 loops=1)

-> Nested Loop
(cost=0.00..201032.88 rows=1 width=130) (actual time=13.086..938463.010
rows=28860 loops=1)

Join Filter:
((m_transaction.m_product_id)::text = (ap_attribute.m_product_id)::text)

-> 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[])))

-> Seq Scan on
ap_attribute (cost=0.00..11820.75 rows=86405 width=85) (actual
time=0.001..20.121 rows=86645 loops=28936)

Filter: (isactive =
'Y'::bpchar)

-> Index Scan using ap_bs_key on
ap_basic (cost=0.00..7.76 rows=1 width=72) (actual time=0.013..0.014
rows=1 loops=28860)

Index Cond:
((ap_basic_id)::text = (ap_attribute.ap_basic_id)::text)

-> Index Scan using
m_productprice_pricelist_ve_un on m_productprice (cost=0.00..8.40
rows=1 width=36) (actual time=0.042..0.042 rows=1 loops=28860)

Index Cond:
(((m_pricelist_version_id)::text =
'69CBAFD24AB24E3CB9B9A44E282D62EC'::text) AND
((m_transaction.m_product_id)::text = (m_product_id)::text))

-> Index Scan using m_inoutline_key on
m_inoutline (cost=0.00..9.27 rows=1 width=66) (actual time=0.120..0.120
rows=1 loops=28860)

Index Cond: ((m_inoutline_id)::text =
(m_transaction.m_inoutline_id)::text)

-> Index Scan using m_inout_key on m_inout
(cost=0.00..7.79 rows=1 width=72) (actual time=0.015..0.015 rows=0
loops=28860)

Index Cond: ((m_inout_id)::text =
(m_inoutline.m_inout_id)::text)

Filter: (((m_warehouse_id)::text =
'50657AE0F49A421A9F10154C8632A726'::text) AND (issotrx = 'N'::bpchar))

-> Index Scan using c_bpartner_key on c_bpartner
(cost=0.00..7.57 rows=1 width=56) (actual time=0.007..0.008 rows=1
loops=517)

Index Cond: ((c_bpartner_id)::text =
(m_inout.c_bpartner_id)::text)

Total runtime: 944256.536 ms

27 資料列

Total runtime: 944,319.072 ms



Can someone tell me how to performance the query?
Thank s in advance.

Jerome

[robin]

pts rule name description
---- ---------------------- --------------------------------------------------
-20 ALL_TRUSTED Passed through trusted hosts only via SMTP
-1.9 BAYES_00 BODY: Bayes spam probability is 0 to 1%
[score: 0.0000]
X-Pg-Spam-Score: -1.9 (-)
X-List-Archive: <http://archives.postgresql.org/pgsql-admin>
X-List-Help: <mailto:***@postgresql.org?body=help>
X-List-ID: <pgsql-admin.postgresql.org>
X-List-Owner: <mailto:pgsql-admin-***@postgresql.org>
X-List-Post: <mailto:pgsql-***@postgresql.org>
X-List-Subscribe: <mailto:***@postgresql.org?body=sub%20pgsql-admin>
X-List-Unsubscribe: <mailto:***@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
X-Precedence: bulk
Bytes: 4766
Xref: number.nntp.dca.giganews.com pgsql.admin:24785

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

[Tom Lane]

Post by Jerome
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.
-> Seq Scan on
m_transaction (cost=0.00..188132.06 rows=1 width=78) (actual
time=0.015..932.400 rows=28936 loops=1)
((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