Openbravo Issue Tracking System - Retail Modules
View Issue Details
0037245Retail ModulesWeb POSpublic2017-11-07 13:002017-11-20 09:13
normalmajorhave not tried
0037245: Missing index in master.Cashup causes query to be slow
master.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.
 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.
see description.
related to feature request 0035935RR17Q3 closed mtaal Performance reading cashup: set maxresults as only one record is used 
depends on backport 0037246RR17Q4 closed marvintm Missing index in master.Cashup causes query to be slow 
depends on backport 0037247RR17Q3.2 closed marvintm Missing index in master.Cashup causes query to be slow 
Issue History
2017-11-07 13:00ALopeteguiNew Issue
2017-11-07 13:00ALopeteguiAssigned To => Retail
2017-11-07 13:00ALopeteguiResolution time => 1511823600
2017-11-07 13:00ALopeteguiTriggers an Emergency Pack => No
2017-11-07 13:00ALopeteguiNote Added: 0100306
2017-11-07 13:01ALopeteguiTag Attached: Performance
2017-11-07 13:01ALopeteguiStatusnew => scheduled
2017-11-07 13:02ALopeteguiTarget Version => RR18Q1
2017-11-07 13:02ALopeteguiRelationship addedrelated to 0035935
2017-11-08 09:26jorge-garciaStatusscheduled => acknowledged
2017-11-13 16:30SandrahuguetIssue Monitored: Sandrahuguet
2017-11-13 23:30rqueraltaAssigned ToRetail => rqueralta
2017-11-16 20:19hgbotCheckin
2017-11-16 20:19hgbotNote Added: 0100555
2017-11-16 20:19hgbotStatusacknowledged => resolved
2017-11-16 20:19hgbotResolutionopen => fixed
2017-11-16 20:19hgbotFixed in SCM revision => [^]
2017-11-17 11:46marvintmNote Added: 0100574
2017-11-17 11:46marvintmStatusresolved => new
2017-11-17 11:46marvintmResolutionfixed => open
2017-11-17 15:17hgbotCheckin
2017-11-17 15:17hgbotNote Added: 0100595
2017-11-20 09:13marvintmStatusnew => scheduled
2017-11-20 09:13marvintmStatusscheduled => resolved
2017-11-20 09:13marvintmFixed in Version => RR18Q1
2017-11-20 09:13marvintmResolutionopen => fixed
2017-11-20 09:13marvintmReview Assigned To => marvintm
2017-11-20 09:13marvintmStatusresolved => closed

2017-11-07 13:00   
Note: marked as obps as found in BUT performance testing.
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
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.
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