Message-ID: <50E66B33.3070707@countrybright.com.cn>
Date: Fri, 04 Jan 2013 13:40:03 +0800
From: Jerome <iori@countrybright.com.cn>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: my query is so slow,It costs 944,319.072 ms,Can someone Help!!
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

I have a postgresql 9.1 in my server,And here 's my query,It costs=20
944,319.072 ms,it is so slow,query result have 27  records.

SELECT=20
AP_ATTRIBUTE.stylename,AP_BASIC.style,AP_ATTRIBUTE.color,AP_ATTRIBUTE.size,=
=20
AP_BASIC.ap_sizesequence_id as apSizesequenceId,=20
M_TRANSACTION.movementdate as MOVEMENTDATE,=20
SUM(M_TRANSACTION.MOVEMENTQTY) AS MOVEMENTQTY,=20
COALESCE(M_PRODUCTPRICE.pricelist,999999) as pricelist,=20
  M_INOUT.M_INOUT_ID,      M_INOUT.DOCUMENTNO AS=20
DOCUMENTNO,C_BPARTNER.VALUE AS BPNAME, '' AS M_MOVEMENTLINE_ID, '' AS=20
MOVEMENT, '' AS MOVEMENT_DESCRIPTION,      '' AS M_INVENTORY_ID, '' AS=20
M_MOVEMENT_ID, '' AS M_PRODUCTIONPLAN_ID, '' AS PRODUCTION_PLAN
FROM M_TRANSACTION
  left join AP_ATTRIBUTE  ON=20
AP_ATTRIBUTE.M_PRODUCT_ID=3DM_TRANSACTION.M_PRODUCT_ID
  left join AP_BASIC  ON AP_BASIC.AP_BASIC_ID=3DAP_ATTRIBUTE.AP_BASIC_ID
  left join M_PRODUCTPRICE on M_TRANSACTION.M_PRODUCT_ID  =3D=20
M_PRODUCTPRICE.M_PRODUCT_ID AND=20
M_PRODUCTPRICE.m_pricelist_version_id=3D'69CBAFD24AB24E3CB9B9A44E282D62EC',=
 M_INOUTLINE,=20
M_INOUT, C_BPARTNER
WHERE        M_TRANSACTION.M_INOUTLINE_ID =3D=20
M_INOUTLINE.M_INOUTLINE_ID      AND M_INOUTLINE.M_INOUT_ID =3D=20
M_INOUT.M_INOUT_ID      AND M_TRANSACTION.M_INOUTLINE_ID IS NOT=20
NULL       AND M_INOUT.C_BPARTNER_ID=3DC_BPARTNER.C_BPARTNER_ID AND=20
M_TRANSACTION.AD_CLIENT_ID IN=20
('0','FF80818129E99DC80129E9AD546A0708')      AND=20
M_TRANSACTION.AD_ORG_ID IN=20
('44523886B0E0423584BB0E87133F3EE4','4510F92E007E48B28EE6CEB088A78348','0')=
=20
AND AP_ATTRIBUTE.isActive =3D 'Y'      AND 1=3D1  AND=20
M_TRANSACTION.movementdate >=3D to_date('2012-12-01')   AND=20
M_TRANSACTION.movementdate < to_date('2013-01-01')   AND=20
M_INOUT.M_WAREHOUSE_ID =3D'50657AE0F49A421A9F10154C8632A726'  AND=20
M_INOUT.ISSOTRX=3D'N'
GROUP BY=20
AP_ATTRIBUTE.stylename,AP_BASIC.style,AP_ATTRIBUTE.color,AP_ATTRIBUTE.size,=
=20
AP_BASIC.ap_sizesequence_id, M_TRANSACTION.movementdate,=20
M_PRODUCTPRICE.pricelist, M_INOUT.M_INOUT_ID,=20
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,=20
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.=20
index(m_pricelist_version_id,m_product_id)
it have 2000329 rows in M_INOUTLINE. pk:m_inoutline_id,=20
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=3D201073.74..201073.78 rows=3D1 width=3D138) (actual=
=20
time=3D944255.849..944256.284 rows=3D517 loops=3D1)

   ->  Sort  (cost=3D201073.74..201073.74 rows=3D1 width=3D138) (actual=20
time=3D944255.828..944255.859 rows=3D517 loops=3D1)

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

         Sort Method: quicksort  Memory: 162kB

         ->  Nested Loop  (cost=3D0.00..201073.73 rows=3D1 width=3D138)=20
(actual time=3D272067.456..944244.793 rows=3D517 loops=3D1)

               ->  Nested Loop  (cost=3D0.00..201066.14 rows=3D1 width=3D14=
6)=20
(actual time=3D272067.433..944239.495 rows=3D517 loops=3D1)

                     ->  Nested Loop  (cost=3D0.00..201058.34 rows=3D1=20
width=3D107) (actual time=3D13.225..943784.425 rows=3D28860 loops=3D1)

                           ->  Nested Loop Left Join=20
(cost=3D0.00..201049.06 rows=3D1 width=3D107) (actual time=3D13.197..940249=
.631=20
rows=3D28860 loops=3D1)

                                 ->  Nested Loop Left Join=20
(cost=3D0.00..201040.64 rows=3D1 width=3D136) (actual time=3D13.122..938969=
.396=20
rows=3D28860 loops=3D1)

                                       ->  Nested Loop=20
(cost=3D0.00..201032.88 rows=3D1 width=3D130) (actual time=3D13.086..938463=
.010=20
rows=3D28860 loops=3D1)

                                             Join Filter:=20
((m_transaction.m_product_id)::text =3D (ap_attribute.m_product_id)::text)

                                             ->  Seq Scan on=20
m_transaction  (cost=3D0.00..188132.06 rows=3D1 width=3D78) (actual=20
time=3D0.015..932.400 rows=3D28936 loops=3D1)

                                                   Filter:=20
((m_inoutline_id IS NOT NULL) AND ((ad_client_id)::text =3D ANY=20
('{0,FF80818129E99DC80129E9AD546A0708}'::text[])) AND (movementdate >=3D=20
'2012-12-01 00:00:00'::timestamp without time zone) AND (movementdate <=20
'2013-01-01 00:00:00'::timestamp without time zone) AND=20
((ad_org_id)::text =3D ANY=20
('{44523886B0E0423584BB0E87133F3EE4,4510F92E007E48B28EE6CEB088A78348,0}'::t=
ext[])))

                                             ->  Seq Scan on=20
ap_attribute  (cost=3D0.00..11820.75 rows=3D86405 width=3D85) (actual=20
time=3D0.001..20.121 rows=3D86645 loops=3D28936)

                                                   Filter: (isactive =3D=20
'Y'::bpchar)

                                       ->  Index Scan using ap_bs_key on=20
ap_basic  (cost=3D0.00..7.76 rows=3D1 width=3D72) (actual time=3D0.013..0.0=
14=20
rows=3D1 loops=3D28860)

                                             Index Cond:=20
((ap_basic_id)::text =3D (ap_attribute.ap_basic_id)::text)

                                 ->  Index Scan using=20
m_productprice_pricelist_ve_un on m_productprice  (cost=3D0.00..8.40=20
rows=3D1 width=3D36) (actual time=3D0.042..0.042 rows=3D1 loops=3D28860)

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

                           ->  Index Scan using m_inoutline_key on=20
m_inoutline  (cost=3D0.00..9.27 rows=3D1 width=3D66) (actual time=3D0.120..=
0.120=20
rows=3D1 loops=3D28860)

                                 Index Cond: ((m_inoutline_id)::text =3D=20
(m_transaction.m_inoutline_id)::text)

                     ->  Index Scan using m_inout_key on m_inout=20
(cost=3D0.00..7.79 rows=3D1 width=3D72) (actual time=3D0.015..0.015 rows=3D=
0=20
loops=3D28860)

                           Index Cond: ((m_inout_id)::text =3D=20
(m_inoutline.m_inout_id)::text)

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

               ->  Index Scan using c_bpartner_key on c_bpartner=20
(cost=3D0.00..7.57 rows=3D1 width=3D56) (actual time=3D0.007..0.008 rows=3D=
1=20
loops=3D517)

                     Index Cond: ((c_bpartner_id)::text =3D=20
(m_inout.c_bpartner_id)::text)

Total runtime: 944256.536 ms

27 =E8=B3=87=E6=96=99=E5=88=97

Total runtime: 944,319.072 ms


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

Jerome


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin