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

Openbravo Issue Tracking System - Retail Modules

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0037247

Retail Modules

Web POS

public

2017-11-07 13:00

2017-12-22 08:45

Reporter

ALopetegui

Assigned To

marvintm

Priority

normal

Severity

major

Reproducibility

have not tried

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

RR17Q3.2

Fixed in Version

RR17Q3.2

Merge Request Status

Review Assigned To

migueldejuana

OBNetwork customer

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

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.

Additional Information