0037247: 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

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

blocks

defect

0037245

RR18Q1

closed

rqueralta

Missing index in master.Cashup causes query to be slow

Notes
(0101150) 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> openbravo.com> Date: Tue Dec 19 18:52:19 2017 +0100 URL: http://code.openbravo.com/retail/backports/3.0RR17Q3.2/org.openbravo.retail.posterminal/rev/09e7ca7faa0a330eeab824b897c7b931efbbd1ea [^] Fixed issue 37247. Missing index in master.Cashup causes query to be slow --- M src-db/database/model/tables/OBPOS_APP_CASHUP.xml ---

(0101197) migueldejuana (viewer) 2017-12-22 08:45	Reviewed

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	=> http://code.openbravo.com/retail/backports/3.0RR17Q3.2/org.openbravo.retail.posterminal/rev/09e7ca7faa0a330eeab824b897c7b931efbbd1ea [^]
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