Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0037245 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Retail Modules] Web POS | major | have not tried | 2017-11-07 13:00 | 2017-11-20 09:13 | |||
| Reporter | ALopetegui | View Status | public | |||||
| Assigned To | rqueralta | |||||||
| Priority | normal | Resolution | fixed | Fixed in Version | RR18Q1 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | 686a76959900 | ||||
| Projection | none | ETA | none | Target Version | RR18Q1 | |||
| OS | Any | Database | Any | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Merge Request Status | ||||||||
| Review Assigned To | marvintm | |||||||
| OBNetwork customer | OBPS | |||||||
| Support ticket | ||||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0037245: Missing index in master.Cashup causes query to be slow | |||||||
| Description | master.Cashup contains the following query: String hqlCashup = "select c.id, c.netsales as netSales, c.grosssales as grossSales, " + "c.netreturns as netReturns, c.grossreturns as grossReturns, c.totalretailtransactions as totalRetailTransactions," + "c.creationDate as creationDate, c.createdBy.id as userId, c.isbeingprocessed, c.isProcessed, c.pOSTerminal.id as posterminal " + "from OBPOS_App_Cashup c where c.isProcessed=:isprocessed and c.pOSTerminal.id= :terminal " + isprocessedbo + " order by c.creationDate desc"; Other issue 35935 already added missing limit to this query in 17Q3. However only matching index in that table if on obpos_applications_id which forces the db to load all rows for this terminal which will increase over time. As functionally the latest cashup should be always the pending one the following index makes more sense for this query. obpos_applications_id, created That avoid scanning all rows for this terminal and in practice instead only needs a single index fetch (order by created desc limit 1 => last row in index). In practice we will not add a new index as written above but modify the existing index: - obpos_applications_id to be instead: - obpos_applications_id, created As that helps our query but still allows to use that new index like the old one. | |||||||
| Steps To Reproduce | Limit (cost=5017.32..5017.32 rows=1 width=108) (actual time=9.322..9.322 rows=1 loops=1) Buffers: shared hit=4911 -> Sort (cost=5017.32..5017.33 rows=6 width=108) (actual time=9.320..9.320 rows=1 loops=1) Sort Key: created DESC Sort Method: top-N heapsort Memory: 25kB Buffers: shared hit=4911 -> Bitmap Heap Scan on obpos_app_cashup obpos_app_0_ (cost=166.10..5017.29 rows=6 width=108) (actual time=1.835..9.279 rows=86 loops=1) Recheck Cond: ((obpos_applications_id)::text = '4307610354AB4F728B1A27636049E35F'::text) Filter: ((isprocessed = 'N'::bpchar) AND (isprocessedbo = 'N'::bpchar)) Rows Removed by Filter: 8694 Heap Blocks: exact=4831 Buffers: shared hit=4911 -> Bitmap Index Scan on obpos_app_cashup_terminal_idx (cost=0.00..166.10 rows=1273 width=0) (actual time=1.127..1.127 rows=8836 loops=1) Index Cond: ((obpos_applications_id)::text = '4307610354AB4F728B1A27636049E35F'::text) Buffers: shared hit=80 And notice the scanning of 8836 rows of the index and then manual filtering discarding most of those rows. | |||||||
| Proposed Solution | see description. | |||||||
| Tags | Performance | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
||||||||||||||||||||||
|
||||||||||||||||||||||
 Relationships |
|
Notes |
|
|
(0100306) ALopetegui (viewer) 2017-11-07 13:00 |
Note: marked as obps as found in BUT performance testing. |
|
(0100555) hgbot (developer) 2017-11-16 20:19 |
Repository: erp/pmods/org.openbravo.retail.posterminal Changeset: 686a76959900ae4985dd82f86b785dfdcc083f8e Author: Rafael Queralta Pozo <rqueralta <at> nauta.cu> Date: Mon Nov 13 17:12:39 2017 -0500 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/686a76959900ae4985dd82f86b785dfdcc083f8e [^] Fixed issue 37245: Missing index in master.Cashup causes query to be slow --- M src-db/database/model/tables/OBPOS_APP_CASHUP.xml --- |
|
(0100574) marvintm (viewer) 2017-11-17 11:46 |
The fix is not correct. Instead of adding the "created" column to the "OBPOS_APP_CASHUP_PARENT_IDX" index, it should be added to the "OBPOS_APP_CASHUP_TERMINAL_IDX" index. |
|
(0100595) hgbot (developer) 2017-11-17 15:17 |
Repository: erp/pmods/org.openbravo.retail.posterminal Changeset: d100531b2219f6c31dabd6bdc44675299b45b637 Author: Rafael Queralta Pozo <rqueralta <at> nauta.cu> Date: Fri Nov 17 09:13:41 2017 -0500 URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/d100531b2219f6c31dabd6bdc44675299b45b637 [^] Related to issue 37245: Missing index in master.Cashup causes query to be slow --- M src-db/database/model/tables/OBPOS_APP_CASHUP.xml --- |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2017-11-07 13:00 | ALopetegui | New Issue | |
| 2017-11-07 13:00 | ALopetegui | Assigned To | => Retail |
| 2017-11-07 13:00 | ALopetegui | OBNetwork customer | => Yes |
| 2017-11-07 13:00 | ALopetegui | Resolution time | => 1511823600 |
| 2017-11-07 13:00 | ALopetegui | Triggers an Emergency Pack | => No |
| 2017-11-07 13:00 | ALopetegui | Note Added: 0100306 | |
| 2017-11-07 13:01 | ALopetegui | Tag Attached: Performance | |
| 2017-11-07 13:01 | ALopetegui | Status | new => scheduled |
| 2017-11-07 13:02 | ALopetegui | Target Version | => RR18Q1 |
| 2017-11-07 13:02 | ALopetegui | Relationship added | related to 0035935 |
| 2017-11-08 09:26 | jorge-garcia | Status | scheduled => acknowledged |
| 2017-11-13 16:30 | Sandrahuguet | Issue Monitored: Sandrahuguet | |
| 2017-11-13 23:30 | rqueralta | Assigned To | Retail => rqueralta |
| 2017-11-16 20:19 | hgbot | Checkin | |
| 2017-11-16 20:19 | hgbot | Note Added: 0100555 | |
| 2017-11-16 20:19 | hgbot | Status | acknowledged => resolved |
| 2017-11-16 20:19 | hgbot | Resolution | open => fixed |
| 2017-11-16 20:19 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/686a76959900ae4985dd82f86b785dfdcc083f8e [^] |
| 2017-11-17 11:46 | marvintm | Note Added: 0100574 | |
| 2017-11-17 11:46 | marvintm | Status | resolved => new |
| 2017-11-17 11:46 | marvintm | Resolution | fixed => open |
| 2017-11-17 15:17 | hgbot | Checkin | |
| 2017-11-17 15:17 | hgbot | Note Added: 0100595 | |
| 2017-11-20 09:13 | marvintm | Status | new => scheduled |
| 2017-11-20 09:13 | marvintm | Status | scheduled => resolved |
| 2017-11-20 09:13 | marvintm | Fixed in Version | => RR18Q1 |
| 2017-11-20 09:13 | marvintm | Resolution | open => fixed |
| 2017-11-20 09:13 | marvintm | Review Assigned To | => marvintm |
| 2017-11-20 09:13 | marvintm | Status | resolved => closed |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |