Anonymous | Login
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Retail Modules] Web POSmajorhave not tried2017-11-07 13:002017-11-20 09:13
ReporterALopeteguiView Statuspublic 
Assigned Torqueralta 
PrioritynormalResolutionfixedFixed in VersionRR18Q1
StatusclosedFix in branchFixed in SCM revision686a76959900
ProjectionnoneETAnoneTarget VersionRR18Q1
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomarvintm
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0037245: Missing index in master.Cashup causes query to be slow

Descriptionmaster.Cashup contains the following query:
      String hqlCashup = "select, c.netsales as netSales, c.grosssales as grossSales, "
          + "c.netreturns as netReturns, c.grossreturns as grossReturns, c.totalretailtransactions as totalRetailTransactions,"
          + "c.creationDate as creationDate, as userId, c.isbeingprocessed, c.isProcessed, as posterminal "
          + "from OBPOS_App_Cashup c where c.isProcessed=:isprocessed and :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 Solutionsee description.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
related to feature request 0035935RR17Q3 closedmtaal Performance reading cashup: set maxresults as only one record is used 
depends on backport 0037246RR17Q4 closedmarvintm Missing index in master.Cashup causes query to be slow 
depends on backport 0037247RR17Q3.2 closedmarvintm Missing index in master.Cashup causes query to be slow 

-  Notes
ALopetegui (reporter)
2017-11-07 13:00

Note: marked as obps as found in BUT performance testing.
hgbot (developer)
2017-11-16 20:19

Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: 686a76959900ae4985dd82f86b785dfdcc083f8e
Author: Rafael Queralta Pozo <rqueralta <at>>
Date: Mon Nov 13 17:12:39 2017 -0500
URL: [^]

Fixed issue 37245: Missing index in master.Cashup causes query to be slow

M src-db/database/model/tables/OBPOS_APP_CASHUP.xml
marvintm (manager)
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.
hgbot (developer)
2017-11-17 15:17

Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: d100531b2219f6c31dabd6bdc44675299b45b637
Author: Rafael Queralta Pozo <rqueralta <at>>
Date: Fri Nov 17 09:13:41 2017 -0500
URL: [^]

Related to issue 37245: Missing index in master.Cashup causes query to be slow

M src-db/database/model/tables/OBPOS_APP_CASHUP.xml

- 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 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 => [^]
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

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker