Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0036814
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 02. Master data managementmajoralways2017-09-08 12:302017-09-21 16:50
ReporterJONHMView Statuspublic 
Assigned ToAtulOpenbravo 
PriorityhighResolutionfixedFixed in Version3.0PR17Q4
StatusclosedFix in branchFixed in SCM revisioneaf73138f70a
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toaferraz
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0036814: Customer Statement process takes long on environment with big amount of data

DescriptionCustomer Statement process takes long on environment with big amount of data
Steps To Reproduce** Ask for the environment to JONHM

Run the 'Customer Statement' process:
 - Organization = Metal Supermarkets Fairfield
 - Business Partner = 119 Monticelo Ave LLC

It takes for about 6 minutes to finish.
fact_acct = 11427009 records
Proposed SolutionAttached query takes around 9 minutes if it is not cached.
When cached it takes around 10 seconds.

Adding following index, it takes around 800 ms if it is not cached and around 60 ms if it is cached:

CREATE INDEX fact_acct_bpartner
  ON fact_acct
  USING btree
  (c_bpartner_id COLLATE pg_catalog."default")
  WHERE c_bpartner_id IS NOT NULL;
TagsNo tags attached.
Attached Files? file icon customerStatementQuery.sql [^] (6,159 bytes) 2017-09-11 12:23

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0098994)
AtulOpenbravo (developer)
2017-09-12 18:08

 Query plan before index in client environment:-

Sort (cost=3646153.96..3646153.96 rows=1 width=537) (actual time=8476.005..8476.006 rows=11 loops=1)
   Sort Key: (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text END), f.dateacct, f.created
   Sort Method: quicksort Memory: 30kB
   InitPlan 3 (returns $4)
     -> Limit (cost=1.41..25.52 rows=1 width=39) (actual time=0.098..0.098 rows=1 loops=1)
           -> Nested Loop Left Join (cost=1.41..25.52 rows=1 width=39) (actual time=0.096..0.096 rows=1 loops=1)
                 -> Nested Loop Left Join (cost=1.27..25.35 rows=1 width=35) (actual time=0.071..0.071 rows=1 loops=1)
                       -> Nested Loop (cost=0.84..16.89 rows=1 width=33) (actual time=0.050..0.050 rows=1 loops=1)
                             -> Index Only Scan using c_bpartner_idsalesrep on c_bpartner (cost=0.42..8.44 rows=1 width=33) (actual time=0.021..0.021 rows=1 loops=1)
                                   Index Cond: (c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                   Heap Fetches: 1
                             -> Index Scan using c_bplocation_bpartner on c_bpartner_location (cost=0.42..8.45 rows=1 width=66) (actual time=0.026..0.026 rows=1 loops=1)
                                   Index Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                   Filter: ((isactive = 'Y'::bpchar) AND (isbillto = 'Y'::bpchar))
                       -> Index Scan using c_location_key on c_location (cost=0.42..8.44 rows=1 width=68) (actual time=0.019..0.019 rows=1 loops=1)
                             Index Cond: ((c_bpartner_location.c_location_id)::text = (c_location_id)::text)
                 -> Index Scan using c_region_key on c_region (cost=0.14..0.16 rows=1 width=13) (actual time=0.011..0.011 rows=1 loops=1)
                       Index Cond: ((c_location.c_region_id)::text = (c_region_id)::text)
   -> Nested Loop Left Join (cost=1243912.75..3646128.43 rows=1 width=537) (actual time=8427.894..8475.932 rows=11 loops=1)
         -> Nested Loop Left Join (cost=1243912.33..3646055.53 rows=1 width=535) (actual time=8418.898..8429.360 rows=11 loops=1)
               Join Filter: ((o.ad_org_id)::text = (oinfo.ad_org_id)::text)
               -> Nested Loop (cost=1243912.06..3646047.23 rows=1 width=437) (actual time=8418.881..8429.264 rows=11 loops=1)
                     -> Nested Loop (cost=1243911.79..3646038.93 rows=1 width=371) (actual time=8418.869..8429.173 rows=11 loops=1)
                           Join Filter: ((('cus'::text = ANY ('{cus,both}'::text[])) AND (hashed SubPlan 9) AND (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text
END = 'Y'::text)) OR (('cus'::text = ANY ('{ven,both}'::text[])) AND (hashed SubPlan 12) AND (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text END = 'N'::text)))
                           Rows Removed by Join Filter: 41
                           -> Nested Loop (cost=1243751.73..3645852.22 rows=1 width=400) (actual time=8418.210..8424.191 rows=26 loops=1)
                                 -> Nested Loop Left Join (cost=1243751.46..3645843.66 rows=1 width=433) (actual time=8416.137..8418.165 rows=26 loops=1)
                                       -> Nested Loop Left Join (cost=1243751.04..3645841.98 rows=1 width=424) (actual time=8416.122..8418.050 rows=26 loops=1)
                                             -> Nested Loop Left Join (cost=1243750.62..3645833.53 rows=1 width=424) (actual time=8416.091..8417.820 rows=26 loops=1)
                                                   -> Nested Loop Left Join (cost=1243750.19..3645825.08 rows=1 width=398) (actual time=8416.074..8417.548 rows=26 loops=1)
                                                         -> Nested Loop Left Join (cost=1243749.77..3645816.63 rows=1 width=322) (actual time=8416.058..8417.247 rows=26 loops=1)
                                                               -> Nested Loop Left Join (cost=1243749.35..3645808.18 rows=1 width=280) (actual time=8416.029..8416.884 rows=26 loops=1)
                                                                     Join Filter: ((f.c_bpartner_id)::text = (bp.c_bpartner_id)::text)
                                                                     -> Bitmap Heap Scan on fact_acct f (cost=1243748.93..3645799.73 rows=1 width=309) (actual time=8415.987..8416.461 ro
ws=26 loops=1)
                                                                           Recheck Cond: (((ad_table_id)::text = ANY ('{318,D1A97202E832470285C9B1EB026D54E2,4D8C3B3C31D1410DA046140C9F024D
17,B1B7075C46934F0A9FD4C4D0F1457B42}'::text[])) AND ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text))
                                                                           Rows Removed by Index Recheck: 4303107
                                                                           Filter: (((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text) AND (trunc(dateacct) >= '0001-01-01
00:00:00'::timestamp without time zone) AND (trunc(dateacct) <= '9999-09-09 00:00:00'::timestamp without time zone))
                                                                           Rows Removed by Filter: 2355150
                                                                           -> BitmapAnd (cost=1243748.93..1243748.93 rows=2412952 width=0) (actual time=3506.784..3506.784 rows=0 loops=1
)
                                                                                 -> Bitmap Index Scan on fact_acct_table_record_id (cost=0.00..200660.10 rows=6115448 width=0) (actual ti
me=1080.858..1080.858 rows=6096414 loops=1)
                                                                                       Index Cond: ((ad_table_id)::text = ANY ('{318,D1A97202E832470285C9B1EB026D54E2,4D8C3B3C31D1410DA046140C9F024D17,B1B7075C46934F0A9FD4C4D0F1457B42}'::text[]))
                                                                                 -> Bitmap Index Scan on fact_acct_account (cost=0.00..1043088.57 rows=4508786 width=0) (actual time=2366.215..2366.215 rows=4537410 loops=1)
                                                                                       Index Cond: ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text)
                                                                     -> Index Scan using c_bpartner_idsalesrep on c_bpartner bp (cost=0.42..8.44 rows=1 width=70) (actual time=0.013..0.014 rows=1 loops=26)
                                                                           Index Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                                               -> Index Scan using c_invoice_key on c_invoice i (cost=0.42..8.44 rows=1 width=75) (actual time=0.012..0.012 rows=1 loops=26)
                                                                     Index Cond: ((f.record_id)::text = (c_invoice_id)::text)
                                                         -> Index Scan using fin_payment_key on fin_payment fp (cost=0.42..8.44 rows=1 width=109) (actual time=0.010..0.010 rows=0 loops=26)
                                                               Index Cond: ((f.record_id)::text = (fin_payment_id)::text)
                                                   -> Index Scan using fin_finacc_transaction_key on fin_finacc_transaction fft (cost=0.42..8.44 rows=1 width=92) (actual time=0.009..0.009 rows=0 loops=26)
                                                         Index Cond: ((f.record_id)::text = (fin_finacc_transaction_id)::text)
                                             -> Index Scan using fin_payment_schedule_key on fin_payment_schedule ps (cost=0.42..8.44 rows=1 width=66) (actual time=0.008..0.008 rows=0 loops=26)
                                                   Index Cond: ((f.record_id2)::text = (fin_payment_schedule_id)::text)
                                       -> Index Scan using c_invoice_key on c_invoice i2 (cost=0.42..1.67 rows=1 width=75) (actual time=0.003..0.003 rows=0 loops=26)
                                             Index Cond: ((ps.c_invoice_id)::text = (c_invoice_id)::text)
                                 -> Index Scan using ad_org_key on ad_org (cost=0.27..8.54 rows=1 width=32) (actual time=0.230..0.230 rows=1 loops=26)
                                       Index Cond: ((ad_org_id)::text = (f.ad_org_id)::text)
                                       Filter: (ad_isorgincluded(ad_org_id, '428125A1CFA94E27829FF69A54D8E040'::character varying, ad_client_id) <> (-1)::numeric)
                           -> Function Scan on generate_series a (cost=0.00..15.00 rows=333 width=4) (actual time=0.005..0.166 rows=2 loops=26)
                                 Filter: ((value)::numeric < 3::numeric)
                                 Rows Removed by Filter: 1022
                           SubPlan 9
                             -> Seq Scan on c_validcombination (cost=17.14..79.20 rows=329 width=33) (actual time=0.223..0.544 rows=2 loops=1)
                                   Filter: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((hashed SubPlan 7) OR (hashed SubPlan 8)))
                                   Rows Removed by Filter: 1316
                                   SubPlan 7
                                     -> Index Scan using c_bp_customer_acct_bpartner_un on c_bp_customer_acct (cost=0.55..8.57 rows=1 width=33) (actual time=0.036..0.036 rows=1 loops=1)
                                           Index Cond: (((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text) AND ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text))
                                   SubPlan 8
                                     -> Index Scan using c_bp_customer_acct_bpartner_un on c_bp_customer_acct c_bp_customer_acct_1 (cost=0.55..8.57 rows=1 width=33) (actual time=0.014..0.014 rows=1 loops=1)
                                           Index Cond: (((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text) AND ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text))
                           SubPlan 12
                             -> Seq Scan on c_validcombination c_validcombination_1 (cost=17.14..79.20 rows=329 width=33) (never executed)
                                   Filter: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((hashed SubPlan 10) OR (hashed SubPlan 11)))
                                    SubPlan 10
                                     -> Index Scan using c_bp_vendor_acct_acctschema_un on c_bp_vendor_acct (cost=0.55..8.57 rows=1 width=33) (never executed)
                                           Index Cond: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text))
                                   SubPlan 11
                                     -> Index Scan using c_bp_vendor_acct_acctschema_un on c_bp_vendor_acct c_bp_vendor_acct_1 (cost=0.55..8.57 rows=1 width=33) (never executed)
                                           Index Cond: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text))
                     -> Index Scan using ad_org_key on ad_org o (cost=0.27..8.29 rows=1 width=66) (actual time=0.006..0.007 rows=1 loops=11)
                           Index Cond: ((ad_org_id)::text = '428125A1CFA94E27829FF69A54D8E040'::text)
               -> Index Scan using ad_orginfo_key on ad_orginfo oinfo (cost=0.27..8.29 rows=1 width=130) (actual time=0.006..0.007 rows=1 loops=11)
                     Index Cond: ((ad_org_id)::text = '428125A1CFA94E27829FF69A54D8E040'::text)
         -> Index Scan using c_location_key on c_location loc (cost=0.42..8.44 rows=1 width=68) (actual time=0.010..0.011 rows=1 loops=11)
               Index Cond: ((oinfo.c_location_id)::text = (c_location_id)::text)
         SubPlan 1
           -> Aggregate (cost=8.30..8.31 rows=1 width=24) (actual time=0.254..0.255 rows=1 loops=8)
                 -> Index Scan using c_doctype_key on c_doctype (cost=0.28..8.30 rows=1 width=24) (actual time=0.251..0.252 rows=1 loops=8)
                       Index Cond: ((c_doctype_id)::text = (f.c_doctype_id)::text)
         SubPlan 2
           -> Aggregate (cost=8.30..8.31 rows=1 width=13) (actual time=0.015..0.015 rows=1 loops=3)
                 -> Index Scan using ad_ref_list_value on ad_ref_list (cost=0.28..8.30 rows=1 width=13) (actual time=0.012..0.013 rows=1 loops=3)
                       Index Cond: (((ad_reference_id)::text = '183'::text) AND ((value)::text = (f.docbasetype)::text))
         SubPlan 4
           -> Limit (cost=1.27..29.37 rows=1 width=30) (actual time=0.041..0.041 rows=1 loops=11)
                 -> Nested Loop (cost=1.27..29.37 rows=1 width=30) (actual time=0.040..0.040 rows=1 loops=11)
                       -> Nested Loop (cost=0.84..20.92 rows=1 width=45) (actual time=0.026..0.026 rows=1 loops=11)
                             -> Index Only Scan using c_bpartner_idsalesrep on c_bpartner c_bpartner_1 (cost=0.42..8.44 rows=1 width=33) (actual time=0.012..0.012 rows=1 loops=11)
                                   Index Cond: (c_bpartner_id = (oinfo.c_bpartner_id)::text)
                                   Heap Fetches: 11
                             -> Index Scan using ad_user_partner on ad_user (cost=0.42..12.47 rows=1 width=45) (actual time=0.011..0.011 rows=1 loops=11)
                                   Index Cond: ((c_bpartner_id)::text = (oinfo.c_bpartner_id)::text)
                                   Filter: ((isactive = 'Y'::bpchar) AND (em_cdai_recieveinvoice = 'Y'::bpchar))
                       -> Index Scan using c_bplocation_bpartner on c_bpartner_location c_bpartner_location_1 (cost=0.42..8.45 rows=1 width=51) (actual time=0.009..0.009 rows=1 loops=11)
                             Index Cond: ((c_bpartner_id)::text = (oinfo.c_bpartner_id)::text)
                             Filter: ((isactive = 'Y'::bpchar) AND (isbillto = 'Y'::bpchar))
         SubPlan 5
           -> Aggregate (cost=8.44..8.45 rows=1 width=12) (actual time=0.012..0.012 rows=1 loops=11)
                 -> Index Scan using ad_user_key on ad_user ad_user_1 (cost=0.42..8.44 rows=1 width=12) (actual time=0.009..0.010 rows=1 loops=11)
                       Index Cond: ((ad_user_id)::text = (oinfo.ad_user_id)::text)
         SubPlan 6
           -> Aggregate (cost=8.44..8.45 rows=1 width=10) (actual time=0.010..0.010 rows=1 loops=11)
                 -> Index Scan using ad_user_key on ad_user ad_user_2 (cost=0.42..8.44 rows=1 width=10) (actual time=0.007..0.007 rows=1 loops=11)
                       Index Cond: ((ad_user_id)::text = (oinfo.ad_user_id)::text)
 Total runtime: 8477.340 ms
(116 rows)
(0098995)
AtulOpenbravo (developer)
2017-09-12 18:09

 Query plan after index in client environment:-

Sort (cost=2436.85..2436.85 rows=1 width=537) (actual time=50.952..50.953 rows=11 loops=1)
   Sort Key: (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text END), f.dateacct, f.created
   Sort Method: quicksort Memory: 30kB
   InitPlan 3 (returns $4)
     -> Limit (cost=1.41..25.52 rows=1 width=39) (actual time=0.071..0.071 rows=1 loops=1)
           -> Nested Loop Left Join (cost=1.41..25.52 rows=1 width=39) (actual time=0.070..0.070 rows=1 loops=1)
                 -> Nested Loop Left Join (cost=1.27..25.35 rows=1 width=35) (actual time=0.056..0.056 rows=1 loops=1)
                       -> Nested Loop (cost=0.84..16.89 rows=1 width=33) (actual time=0.035..0.035 rows=1 loops=1)
                             -> Index Only Scan using c_bpartner_idsalesrep on c_bpartner (cost=0.42..8.44 rows=1 width=33) (actual time=0.013..0.013 rows=1 loops=1)
                                   Index Cond: (c_bpartner_id = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                   Heap Fetches: 1
                             -> Index Scan using c_bplocation_bpartner on c_bpartner_location (cost=0.42..8.45 rows=1 width=66) (actual time=0.022..0.022 rows=1 loops=1)
                                   Index Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                   Filter: ((isactive = 'Y'::bpchar) AND (isbillto = 'Y'::bpchar))
                       -> Index Scan using c_location_key on c_location (cost=0.42..8.44 rows=1 width=68) (actual time=0.019..0.019 rows=1 loops=1)
                             Index Cond: ((c_bpartner_location.c_location_id)::text = (c_location_id)::text)
                 -> Index Scan using c_region_key on c_region (cost=0.14..0.16 rows=1 width=13) (actual time=0.004..0.004 rows=1 loops=1)
                       Index Cond: ((c_location.c_region_id)::text = (c_region_id)::text)
   -> Nested Loop Left Join (cost=183.85..2411.32 rows=1 width=537) (actual time=5.427..50.901 rows=11 loops=1)
         -> Nested Loop Left Join (cost=183.43..2338.42 rows=1 width=535) (actual time=1.070..10.406 rows=11 loops=1)
               Join Filter: ((o.ad_org_id)::text = (oinfo.ad_org_id)::text)
               -> Nested Loop (cost=183.16..2330.12 rows=1 width=437) (actual time=1.061..10.320 rows=11 loops=1)
                     -> Nested Loop (cost=182.89..2321.82 rows=1 width=371) (actual time=1.052..10.233 rows=11 loops=1)
                           Join Filter: ((('cus'::text = ANY ('{cus,both}'::text[])) AND (hashed SubPlan 9) AND (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text
END = 'Y'::text)) OR (('cus'::text = ANY ('{ven,both}'::text[])) AND (hashed SubPlan 12) AND (CASE WHEN ((a.value)::numeric = 1::numeric) THEN 'Y'::text ELSE 'N'::text END = 'N'::text)))
                           Rows Removed by Join Filter: 41
                           -> Nested Loop (cost=22.83..2135.11 rows=1 width=400) (actual time=0.409..5.304 rows=26 loops=1)
                                 -> Nested Loop Left Join (cost=22.56..2126.55 rows=1 width=433) (actual time=0.194..1.983 rows=26 loops=1)
                                       -> Nested Loop Left Join (cost=22.14..2124.87 rows=1 width=424) (actual time=0.180..1.870 rows=26 loops=1)
                                             -> Nested Loop Left Join (cost=21.72..2116.42 rows=1 width=424) (actual time=0.163..1.700 rows=26 loops=1)
                                                   -> Nested Loop Left Join (cost=21.29..2107.97 rows=1 width=398) (actual time=0.151..1.467 rows=26 loops=1)
                                                         -> Nested Loop Left Join (cost=20.87..2099.52 rows=1 width=322) (actual time=0.142..1.211 rows=26 loops=1)
                                                               -> Nested Loop Left Join (cost=20.45..2091.07 rows=1 width=280) (actual time=0.124..0.905 rows=26 loops=1)
                                                                     Join Filter: ((f.c_bpartner_id)::text = (bp.c_bpartner_id)::text)
                                                                     -> Bitmap Heap Scan on fact_acct f (cost=20.03..2082.62 rows=1 width=309) (actual time=0.096..0.504 rows=26 loops=1)
                                                                           Recheck Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                                                           Filter: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((ad_table_id)::text = ANY ('{
318,D1A97202E832470285C9B1EB026D54E2,4D8C3B3C31D1410DA046140C9F024D17,B1B7075C46934F0A9FD4C4D0F1457B42}'::text[])) AND (trunc(dateacct) >= '0001-01-01 00:00:00'::timestamp without time zo
ne) AND (trunc(dateacct) <= '9999-09-09 00:00:00'::timestamp without time zone))
                                                                            Rows Removed by Filter: 29
                                                                           -> Bitmap Index Scan on fact_acct_bpartner (cost=0.00..20.02 rows=462 width=0) (actual time=0.049..0.049 rows=
55 loops=1)
                                                                                 Index Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                                                     -> Index Scan using c_bpartner_idsalesrep on c_bpartner bp (cost=0.42..8.44 rows=1 width=70) (actual time=0.013..0.0
13 rows=1 loops=26)
                                                                           Index Cond: ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text)
                                                               -> Index Scan using c_invoice_key on c_invoice i (cost=0.42..8.44 rows=1 width=75) (actual time=0.010..0.010 rows=1 loops=
26)
                                                                     Index Cond: ((f.record_id)::text = (c_invoice_id)::text)
                                                         -> Index Scan using fin_payment_key on fin_payment fp (cost=0.42..8.44 rows=1 width=109) (actual time=0.008..0.009 rows=0 loops=
26)
                                                               Index Cond: ((f.record_id)::text = (fin_payment_id)::text)
                                                   -> Index Scan using fin_finacc_transaction_key on fin_finacc_transaction fft (cost=0.42..8.44 rows=1 width=92) (actual time=0.007..0.0
07 rows=0 loops=26)
                                                         Index Cond: ((f.record_id)::text = (fin_finacc_transaction_id)::text)
                                             -> Index Scan using fin_payment_schedule_key on fin_payment_schedule ps (cost=0.42..8.44 rows=1 width=66) (actual time=0.005..0.005 rows=0 l
oops=26)
                                                   Index Cond: ((f.record_id2)::text = (fin_payment_schedule_id)::text)
                                       -> Index Scan using c_invoice_key on c_invoice i2 (cost=0.42..1.67 rows=1 width=75) (actual time=0.003..0.003 rows=0 loops=26)
                                             Index Cond: ((ps.c_invoice_id)::text = (c_invoice_id)::text)
                                 -> Index Scan using ad_org_key on ad_org (cost=0.27..8.54 rows=1 width=32) (actual time=0.126..0.127 rows=1 loops=26)
                                       Index Cond: ((ad_org_id)::text = (f.ad_org_id)::text)
                                       Filter: (ad_isorgincluded(ad_org_id, '428125A1CFA94E27829FF69A54D8E040'::character varying, ad_client_id) <> (-1)::numeric)
                           -> Function Scan on generate_series a (cost=0.00..15.00 rows=333 width=4) (actual time=0.004..0.164 rows=2 loops=26)
                                 Filter: ((value)::numeric < 3::numeric)
                                 Rows Removed by Filter: 1022
                           SubPlan 9
                             -> Seq Scan on c_validcombination (cost=17.14..79.20 rows=329 width=33) (actual time=0.213..0.531 rows=2 loops=1)
                                   Filter: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((hashed SubPlan 7) OR (hashed SubPlan 8)))
                                   Rows Removed by Filter: 1316
                                   SubPlan 7
                                     -> Index Scan using c_bp_customer_acct_bpartner_un on c_bp_customer_acct (cost=0.55..8.57 rows=1 width=33) (actual time=0.018..0.018 rows=1 loops=1)
                                           Index Cond: (((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text) AND ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52
'::text))
                                   SubPlan 8
                                     -> Index Scan using c_bp_customer_acct_bpartner_un on c_bp_customer_acct c_bp_customer_acct_1 (cost=0.55..8.57 rows=1 width=33) (actual time=0.013..
0.013 rows=1 loops=1)
                                           Index Cond: (((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text) AND ((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52
'::text))
                           SubPlan 12
                             -> Seq Scan on c_validcombination c_validcombination_1 (cost=17.14..79.20 rows=329 width=33) (never executed)
                                   Filter: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((hashed SubPlan 10) OR (hashed SubPlan 11)))
                                   SubPlan 10
                                     -> Index Scan using c_bp_vendor_acct_acctschema_un on c_bp_vendor_acct (cost=0.55..8.57 rows=1 width=33) (never executed)
                                           Index Cond: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text))
                                   SubPlan 11
                                     -> Index Scan using c_bp_vendor_acct_acctschema_un on c_bp_vendor_acct c_bp_vendor_acct_1 (cost=0.55..8.57 rows=1 width=33) (never executed)
                                           Index Cond: (((c_acctschema_id)::text = 'A73ACFCC9789412392625CE75FB6CE52'::text) AND ((c_bpartner_id)::text = '4B90BC2D3F864310B3E5D8CD47695635'::text))
                     -> Index Scan using ad_org_key on ad_org o (cost=0.27..8.29 rows=1 width=66) (actual time=0.006..0.006 rows=1 loops=11)
                           Index Cond: ((ad_org_id)::text = '428125A1CFA94E27829FF69A54D8E040'::text)
               -> Index Scan using ad_orginfo_key on ad_orginfo oinfo (cost=0.27..8.29 rows=1 width=130) (actual time=0.006..0.006 rows=1 loops=11)
                     Index Cond: ((ad_org_id)::text = '428125A1CFA94E27829FF69A54D8E040'::text)
         -> Index Scan using c_location_key on c_location loc (cost=0.42..8.44 rows=1 width=68) (actual time=0.009..0.009 rows=1 loops=11)
               Index Cond: ((oinfo.c_location_id)::text = (c_location_id)::text)
         SubPlan 1
           -> Aggregate (cost=8.30..8.31 rows=1 width=24) (actual time=0.010..0.010 rows=1 loops=8)
                 -> Index Scan using c_doctype_key on c_doctype (cost=0.28..8.30 rows=1 width=24) (actual time=0.007..0.008 rows=1 loops=8)
                       Index Cond: ((c_doctype_id)::text = (f.c_doctype_id)::text)
         SubPlan 2
           -> Aggregate (cost=8.30..8.31 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=3)
                 -> Index Scan using ad_ref_list_value on ad_ref_list (cost=0.28..8.30 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=3)
                       Index Cond: (((ad_reference_id)::text = '183'::text) AND ((value)::text = (f.docbasetype)::text))
         SubPlan 4
           -> Limit (cost=1.27..29.37 rows=1 width=30) (actual time=0.038..0.039 rows=1 loops=11)
                 -> Nested Loop (cost=1.27..29.37 rows=1 width=30) (actual time=0.038..0.038 rows=1 loops=11)
                       -> Nested Loop (cost=0.84..20.92 rows=1 width=45) (actual time=0.025..0.025 rows=1 loops=11)
                             -> Index Only Scan using c_bpartner_idsalesrep on c_bpartner c_bpartner_1 (cost=0.42..8.44 rows=1 width=33) (actual time=0.012..0.012 rows=1 loops=11)
                                   Index Cond: (c_bpartner_id = (oinfo.c_bpartner_id)::text)
                                   Heap Fetches: 11
                             -> Index Scan using ad_user_partner on ad_user (cost=0.42..12.47 rows=1 width=45) (actual time=0.010..0.010 rows=1 loops=11)
                                   Index Cond: ((c_bpartner_id)::text = (oinfo.c_bpartner_id)::text)
                                   Filter: ((isactive = 'Y'::bpchar) AND (em_cdai_recieveinvoice = 'Y'::bpchar))
                       -> Index Scan using c_bplocation_bpartner on c_bpartner_location c_bpartner_location_1 (cost=0.42..8.45 rows=1 width=51) (actual time=0.009..0.009 rows=1 loops=11)
                             Index Cond: ((c_bpartner_id)::text = (oinfo.c_bpartner_id)::text)
                             Filter: ((isactive = 'Y'::bpchar) AND (isbillto = 'Y'::bpchar))
         SubPlan 5
           -> Aggregate (cost=8.44..8.45 rows=1 width=12) (actual time=0.011..0.011 rows=1 loops=11)
                 -> Index Scan using ad_user_key on ad_user ad_user_1 (cost=0.42..8.44 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=11)
                       Index Cond: ((ad_user_id)::text = (oinfo.ad_user_id)::text)
         SubPlan 6
           -> Aggregate (cost=8.44..8.45 rows=1 width=10) (actual time=0.009..0.010 rows=1 loops=11)
                 -> Index Scan using ad_user_key on ad_user ad_user_2 (cost=0.42..8.44 rows=1 width=10) (actual time=0.007..0.008 rows=1 loops=11)
                       Index Cond: ((ad_user_id)::text = (oinfo.ad_user_id)::text)
 Total runtime: 51.749 ms
(112 rows)
(0098997)
hgbot (developer)
2017-09-12 18:37

 Repository: erp/devel/pi
Changeset: eaf73138f70a7bea9add012fbe5b7d6f02783550
Author: Atul Gaware <atul.gaware <at> openbravo.com>
Date: Tue Sep 12 21:41:29 2017 +0530
URL: http://code.openbravo.com/erp/devel/pi/rev/eaf73138f70a7bea9add012fbe5b7d6f02783550 [^]

Fixes issue 36814: Customer Statement process takes long

Add index for c_bpartner_id column in fact_acct table

---
M src-db/database/model/tables/FACT_ACCT.xml
---
(0099003)
hgbot (developer)
2017-09-13 09:27

 Repository: erp/devel/pi
Changeset: 29adad2927d4ca09ca93b91343e130e49f4d7b9f
Author: Alvaro Ferraz <alvaro.ferraz <at> openbravo.com>
Date: Wed Sep 13 09:26:50 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/29adad2927d4ca09ca93b91343e130e49f4d7b9f [^]

Related to issue 36814: Use partial index

---
M src-db/database/model/tables/FACT_ACCT.xml
---
(0099004)
aferraz (manager)
2017-09-13 09:27

 Code review + Testing OK
(0099382)
hudsonbot (developer)
2017-09-21 16:49

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/9750b78d3e5c [^]
Maturity status: Test
(0099384)
hudsonbot (developer)
2017-09-21 16:50

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/9750b78d3e5c [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2017-09-08 12:30 JONHM New Issue
2017-09-08 12:30 JONHM Assigned To => Triage Finance
2017-09-08 12:30 JONHM Modules => Core
2017-09-08 12:30 JONHM Resolution time => 1506636000
2017-09-08 12:30 JONHM Triggers an Emergency Pack => No
2017-09-11 12:23 JONHM File Added: customerStatementQuery.sql
2017-09-11 19:02 aferraz Assigned To Triage Finance => AtulOpenbravo
2017-09-11 19:09 aferraz Proposed Solution updated
2017-09-12 18:07 AtulOpenbravo Status new => scheduled
2017-09-12 18:08 AtulOpenbravo Note Added: 0098994
2017-09-12 18:09 AtulOpenbravo Note Added: 0098995
2017-09-12 18:37 hgbot Checkin
2017-09-12 18:37 hgbot Note Added: 0098997
2017-09-12 18:37 hgbot Status scheduled => resolved
2017-09-12 18:37 hgbot Resolution open => fixed
2017-09-12 18:37 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/eaf73138f70a7bea9add012fbe5b7d6f02783550 [^]
2017-09-12 18:38 aferraz Review Assigned To => aferraz
2017-09-12 18:38 aferraz Note Added: 0098998
2017-09-12 18:38 aferraz Status resolved => closed
2017-09-12 18:38 aferraz Fixed in Version => 3.0PR17Q4
2017-09-13 09:16 aferraz Proposed Solution updated
2017-09-13 09:24 aferraz Note Deleted: 0098998
2017-09-13 09:27 hgbot Checkin
2017-09-13 09:27 hgbot Note Added: 0099003
2017-09-13 09:27 aferraz Note Added: 0099004
2017-09-21 16:49 hudsonbot Checkin
2017-09-21 16:49 hudsonbot Note Added: 0099382
2017-09-21 16:50 hudsonbot Checkin
2017-09-21 16:50 hudsonbot Note Added: 0099384

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker