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

View Issue DetailsJump to Notes ] Issue History ] Print ]
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Retail Modules] Web POSmajorhave not tried2017-11-07 13:002017-12-22 08:45
ReporterALopeteguiView Statuspublic 
Assigned Tomarvintm 
PrioritynormalResolutionfixedFixed in VersionRR17Q3.2
StatusclosedFix in branchFixed in SCM revision09e7ca7faa0a
ProjectionnoneETAnoneTarget VersionRR17Q3.2
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tomigueldejuana
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo

0037247: 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.
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
blocks defect 0037245RR18Q1 closedrqueralta Missing index in master.Cashup causes query to be slow 

-  Notes
hgbot (developer)
2017-12-19 18:52

Repository: retail/backports/3.0RR17Q3.2/org.openbravo.retail.posterminal
Changeset: 09e7ca7faa0a330eeab824b897c7b931efbbd1ea
Author: Antonio Moreno <antonio.moreno <at>>
Date: Tue Dec 19 18:52:19 2017 +0100
URL: [^]

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

M src-db/database/model/tables/OBPOS_APP_CASHUP.xml
migueldejuana (developer)
2017-12-22 08:45


- Issue History
Date Modified Username Field Change
2017-11-07 13:01 ALopetegui Type defect => backport
2017-11-07 13:01 ALopetegui Target Version => RR17Q3.2
2017-12-19 18:40 marvintm Assigned To Retail => marvintm
2017-12-19 18:52 hgbot Checkin
2017-12-19 18:52 hgbot Note Added: 0101150
2017-12-19 18:52 hgbot Status scheduled => resolved
2017-12-19 18:52 hgbot Resolution open => fixed
2017-12-19 18:52 hgbot Fixed in SCM revision => [^]
2017-12-22 08:45 migueldejuana Review Assigned To => migueldejuana
2017-12-22 08:45 migueldejuana Note Added: 0101197
2017-12-22 08:45 migueldejuana Status resolved => closed
2017-12-22 08:45 migueldejuana Fixed in Version => RR17Q3.2

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker