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
--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
