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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0035210
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] 07. Sales managementmajorhave not tried2017-02-09 09:592017-05-29 17:43
ReporteralostaleView Statuspublic 
Assigned Tomarkmm82 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q3
StatusclosedFix in branchFixed in SCM revisionf8cf0b0e4dc0
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava 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

0035210: 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)
TagsPerformance
Attached Filesdiff file icon 35210.diff [^] (1,106 bytes) 2017-02-09 10:17 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]
depends on feature request 00346503.0PR17Q2 closedcaristu Ability to generate indexes to speed up searching using 'contains' operators 
depends on feature request 00301793.0PR15Q4 closedAugustoMauch DBSourceManager support indexes with functions 
blocks defect 0035636 closedmarkmm82 Business partner selector get stuck in create lines from invoice in hgvol environment 

-  Notes
(0095834)
hgbot (developer)
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 (manager)
2017-04-04 19:22

 Code review + Testing OK
(0096140)
hgbot (developer)
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 (developer)
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 (developer)
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

- Issue History
Date Modified Username Field Change
2017-02-09 09:59 alostale New Issue
2017-02-09 09:59 alostale Assigned To => Triage Finance
2017-02-09 09:59 alostale Modules => Core
2017-02-09 09:59 alostale Triggers an Emergency Pack => No
2017-02-09 10:00 alostale Description Updated View Revisions
2017-02-09 10:00 alostale Tag Attached: Performance
2017-02-09 10:04 alostale Relationship added depends on 0034650
2017-02-09 10:05 alostale Relationship added depends on 0030179
2017-02-09 10:11 alostale Steps to Reproduce Updated View Revisions
2017-02-09 10:17 alostale File Added: 35210.diff
2017-02-09 10:17 alostale Proposed Solution updated
2017-02-09 11:25 caristu Proposed Solution updated
2017-02-09 11:25 caristu Proposed Solution updated
2017-03-31 09:53 dmitry_mezentsev Regression date => 2017-04-14
2017-03-31 10:27 aferraz Resolution time => 1492120800
2017-03-31 10:27 aferraz Regression date 2017-04-14 =>
2017-03-31 12:24 aferraz Assigned To Triage Finance => markmm82
2017-03-31 16:13 markmm82 Status new => scheduled
2017-04-04 19:20 hgbot Checkin
2017-04-04 19:20 hgbot Note Added: 0095834
2017-04-04 19:20 hgbot Status scheduled => resolved
2017-04-04 19:20 hgbot Resolution open => fixed
2017-04-04 19:20 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/f8cf0b0e4dc04e80220d77f2ec1663f5f9052c1c [^]
2017-04-04 19:22 aferraz Review Assigned To => aferraz
2017-04-04 19:22 aferraz Note Added: 0095835
2017-04-04 19:22 aferraz Status resolved => closed
2017-04-04 19:22 aferraz Fixed in Version => 3.0PR17Q3
2017-04-12 14:06 aferraz Relationship added related to 0035636
2017-04-12 14:50 aferraz Relationship replaced blocks 0035636
2017-04-24 18:59 hgbot Checkin
2017-04-24 18:59 hgbot Note Added: 0096140
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096864
2017-05-29 17:43 hudsonbot Checkin
2017-05-29 17:43 hudsonbot Note Added: 0096905

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker