Openbravo Issue Tracking System - Openbravo ERP |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0036814 | Openbravo ERP | 02. Master data management | public | 2017-09-08 12:30 | 2017-09-21 16:50 |
|
| Reporter | JONHM | |
| Assigned To | AtulOpenbravo | |
| Priority | high | Severity | major | Reproducibility | always |
| Status | closed | Resolution | fixed | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | | Fixed in Version | 3.0PR17Q4 | |
| Merge Request Status | |
| Review Assigned To | aferraz |
| OBNetwork customer | |
| Web browser | |
| Modules | Core |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0036814: Customer Statement process takes long on environment with big amount of data |
| Description | Customer 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 Solution | Attached 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; |
| Additional Information | |
| Tags | No tags attached. |
| Relationships | |
| Attached Files |  customerStatementQuery.sql (6,159) 2017-09-11 12:23
https://issues.openbravo.com/file_download.php?file_id=11052&type=bug |
|
| 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 | |
|
Notes |
|
|
|
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) |
|
|
|
|
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
|
|
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
|
|
2017-09-13 09:27
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|