Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035210Openbravo ERP07. Sales managementpublic2017-02-09 09:592017-05-29 17:43
Reporteralostale 
Assigned Tomarkmm82 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q3 
Merge Request Status
Review Assigned Toaferraz
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035210: sequential scans in c_bpartner using partner selector drop down filter
DescriptionWhenever business partner selector is filtered using drop down list, a sequential scan is performed on BP.

This problem has been found in a customer with ~120K BPs.
Steps To Reproduce1. Open Sales Order window
2. Create a record
3. in psql reset stats: select pg_stat_reset();
4. Type "alimentos" in BP selector text field
5. in psql check stats for bp table: select seq_scan, idx_scan from pg_stat_user_tables where relname = 'c_bpartner';
  -> check it is sequentially scaned
Proposed SolutionCreate trgm indexes on bp name and value (find them in attached diff).

QUERY
*****
EXPLAIN SELECT businesspa0_.name AS col_0_0_,
       businesspa0_.c_bpartner_id AS col_1_0_,
       businesspa0_.value AS col_2_0_,
       businesspa0_.so_creditlimit - businesspa0_.so_creditused AS col_3_0_,
       businesspa0_.so_creditused AS col_4_0_,
       businesspa0_.iscustomer AS col_5_0_,
       businesspa0_.isvendor AS col_6_0_,
       businesspa1_.name AS col_7_0_
FROM c_bpartner businesspa0_,
       c_bp_group businesspa1_
WHERE businesspa0_.c_bp_group_id = businesspa1_.c_bp_group_id
       AND ( businesspa0_.ad_client_id IN ( '0', '9D11C7AED56349F186249FDAD0B69515' ) )
       AND ( businesspa0_.ad_org_id IN (
                   '0', '633D90BC32214B03BC67F88F9BB6CF47',
                   '95A2353A5223434982403F0700489AAE',
                   '2BB23DF1D10743BD944C3E643F81A21D',
                   '9BE0ED62B4EC41E0AA0258877627B882',
                   'B3C7EF2F11B4443EA3EF7EFD6EE158A2',
                   '22283C0E837744B6A95DE7798529D149' ) )
       AND ( Upper(businesspa0_.value) LIKE Upper('%AAA%BBB%CCC%')
              OR Upper(businesspa0_.name) LIKE Upper('%AAA%BBB%CCC%')
           )
       AND businesspa0_.isvendor = 'Y'
       AND businesspa0_.issummary = 'N'
       AND businesspa0_.isactive = 'Y'
ORDER BY 1
LIMIT 76;

ORIGINAL PLAN
*************
Limit (cost=23409.84..23409.85 rows=1 width=100)
   -> Sort (cost=23409.84..23409.85 rows=1 width=100)
         Sort Key: businesspa0_.name
         -> Nested Loop (cost=0.00..23409.83 rows=1 width=100)
               Join Filter: ((businesspa0_.c_bp_group_id)::text = (businesspa1_.c_bp_group_id)::text)
               -> Seq Scan on c_bpartner businesspa0_ (cost=0.00..23408.54 rows=1 width=119)
                     Filter: (((ad_client_id)::text = ANY ('{0,9D11C7AED56349F186249FDAD0B69515}'::text[])) AND (isvendor = 'Y'::bpchar) AND (isactive = 'Y'::bpchar) AND (issummary = 'N'::bpchar) AND ((ad_org_id)::text = ANY ('{0,633D90BC32214B03BC67F88F9BB6CF47,95A2353A5223434982403F0700489AAE,2BB23DF1D10743BD944C3E643F81A21D,9BE0ED62B4EC41E0AA0258877627B882,B3C7EF2F11B4443EA3EF7EFD6EE158A2,22283C0E837744B6A95DE7798529D149}'::text[])) AND ((upper((value)::text) ~~ '%AAA%BBB%CCC%'::text) OR (upper((name)::text) ~~ '%AAA%BBB%CCC%'::text)))
               -> Seq Scan on c_bp_group businesspa1_ (cost=0.00..1.13 rows=13 width=47)

PLAN WITH INDEX PG_TRGM
***********************
CREATE INDEX trgm_bpartner_name ON c_bpartner USING gin (UPPER(name) gin_trgm_ops);
CREATE INDEX trgm_bpartner_value ON c_bpartner USING gin (UPPER(value) gin_trgm_ops);

 Limit (cost=451.85..451.85 rows=1 width=100)
   -> Sort (cost=451.85..451.85 rows=1 width=100)
         Sort Key: businesspa0_.name
         -> Nested Loop (cost=356.18..451.84 rows=1 width=100)
               Join Filter: ((businesspa0_.c_bp_group_id)::text = (businesspa1_.c_bp_group_id)::text)
               -> Bitmap Heap Scan on c_bpartner businesspa0_ (cost=356.18..450.54 rows=1 width=119)
                     Recheck Cond: ((upper((value)::text) ~~ '%AAA%BBB%CCC%'::text) OR (upper((name)::text) ~~ '%AAA%BBB%CCC%'::text))
                     Filter: (((ad_client_id)::text = ANY ('{0,9D11C7AED56349F186249FDAD0B69515}'::text[])) AND (isvendor = 'Y'::bpchar) AND (isactive = 'Y'::bpchar) AND (issummary = 'N'::bpchar) AND ((ad_org_id)::text = ANY ('{0,633D90BC32214B03BC67F88F9BB6CF47,95A2353A5223434982403F0700489AAE,2BB23DF1D10743BD944C3E643F81A21D,9BE0ED62B4EC41E0AA0258877627B882,B3C7EF2F11B4443EA3EF7EFD6EE158A2,22283C0E837744B6A95DE7798529D149}'::text[])))
                     -> BitmapOr (cost=356.18..356.18 rows=24 width=0)
                           -> Bitmap Index Scan on trgm_bpartner_value (cost=0.00..172.09 rows=12 width=0)
                                 Index Cond: (upper((value)::text) ~~ '%AAA%BBB%CCC%'::text)
                           -> Bitmap Index Scan on trgm_bpartner_name (cost=0.00..184.09 rows=12 width=0)
                                 Index Cond: (upper((name)::text) ~~ '%AAA%BBB%CCC%'::text)
               -> Seq Scan on c_bp_group businesspa1_ (cost=0.00..1.13 rows=13 width=47)
Additional Information
TagsPerformance
Relationships
depends on feature request 00346503.0PR17Q2 closed caristu Ability to generate indexes to speed up searching using 'contains' operators 
depends on feature request 00301793.0PR15Q4 closed AugustoMauch DBSourceManager support indexes with functions 
blocks defect 0035636 closed markmm82 Business partner selector get stuck in create lines from invoice in hgvol environment 
Attached Filesdiff 35210.diff (1,106) 2017-02-09 10:17
https://issues.openbravo.com/file_download.php?file_id=10431&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-02-09 09:59alostaleNew Issue
2017-02-09 09:59alostaleAssigned To => Triage Finance
2017-02-09 09:59alostaleModules => Core
2017-02-09 09:59alostaleTriggers an Emergency Pack => No
2017-02-09 10:00alostaleDescription Updatedbug_revision_view_page.php?rev_id=14526#r14526
2017-02-09 10:00alostaleTag Attached: Performance
2017-02-09 10:04alostaleRelationship addeddepends on 0034650
2017-02-09 10:05alostaleRelationship addeddepends on 0030179
2017-02-09 10:11alostaleSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=14528#r14528
2017-02-09 10:17alostaleFile Added: 35210.diff
2017-02-09 10:17alostaleProposed Solution updated
2017-02-09 11:25caristuProposed Solution updated
2017-02-09 11:25caristuProposed Solution updated
2017-03-31 09:53dmitry_mezentsevRegression date => 2017-04-14
2017-03-31 10:27aferrazResolution time => 1492120800
2017-03-31 10:27aferrazRegression date2017-04-14 =>
2017-03-31 12:24aferrazAssigned ToTriage Finance => markmm82
2017-03-31 16:13markmm82Statusnew => scheduled
2017-04-04 19:20hgbotCheckin
2017-04-04 19:20hgbotNote Added: 0095834
2017-04-04 19:20hgbotStatusscheduled => resolved
2017-04-04 19:20hgbotResolutionopen => fixed
2017-04-04 19:20hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/f8cf0b0e4dc04e80220d77f2ec1663f5f9052c1c [^]
2017-04-04 19:22aferrazReview Assigned To => aferraz
2017-04-04 19:22aferrazNote Added: 0095835
2017-04-04 19:22aferrazStatusresolved => closed
2017-04-04 19:22aferrazFixed in Version => 3.0PR17Q3
2017-04-12 14:06aferrazRelationship addedrelated to 0035636
2017-04-12 14:50aferrazRelationship replacedblocks 0035636
2017-04-24 18:59hgbotCheckin
2017-04-24 18:59hgbotNote Added: 0096140
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096864
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096905

Notes
(0095834)
hgbot   
2017-04-04 19:20   
Repository: erp/devel/pi
Changeset: f8cf0b0e4dc04e80220d77f2ec1663f5f9052c1c
Author: Mark <markmm82 <at> gmail.com>
Date: Mon Apr 03 09:41:35 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/f8cf0b0e4dc04e80220d77f2ec1663f5f9052c1c [^]

Fixes issue 35210: Sequential scans in c_bpartner using BP selector filter

Created trgm indexes on bp name and bp value column to improve the performance
when business partner selector is filtered using drop down list.

---
M src-db/database/model/tables/C_BPARTNER.xml
---
(0095835)
aferraz   
2017-04-04 19:22   
Code review + Testing OK
(0096140)
hgbot   
2017-04-24 18:59   
Repository: erp/devel/pi
Changeset: e833242c1051edf0af778d1621f27b44ac1bed3b
Author: Mark <markmm82 <at> gmail.com>
Date: Sun Apr 16 13:16:12 2017 -0400
URL: http://code.openbravo.com/erp/devel/pi/rev/e833242c1051edf0af778d1621f27b44ac1bed3b [^]

Fixes issue 35636: Business partner selector get stuck in create lines from
invoice in hgvol environment

In high volume environment, when the Business Partner selector is opened from Create
lines from process in the Invoice window, it takes too long to load results and as
consequence the browser was stocked.

To improve the performance in this selector, was changed the BusinessPartner_data.xsql
methods to use UPPER instead of C_IGNORE_ACCENT function, this way it uses the index
created in issue 35210 and improves of performance it introduces.

---
M src/org/openbravo/erpCommon/info/BusinessPartner_data.xsql
---
(0096864)
hudsonbot   
2017-05-29 17:43   
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/1ee70113bdc4 [^]
Maturity status: Test
(0096905)
hudsonbot   
2017-05-29 17:43   
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/1ee70113bdc4 [^]
Maturity status: Test