Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0042392Openbravo ERPA. Platformpublic2019-11-25 16:482021-02-09 10:50
ReporterioritzCia 
Assigned Tocberner 
PriorityhighSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS30OS VersionUbuntu 18.04
Product Version 
Target VersionFixed in VersionPR21Q1 
Merge Request Statusapproved
Review Assigned To
OBNetwork customerGold
Web browserGoogle Chrome
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0042392: Create index to improve queries over c_import_entry
DescriptionThere are a lot of queries that filter by typeofdata and importstatus. There is already an index by (typeofdata, created), but it would be nice to have one by (importstatus, typeofdata) in this order. Having it in this order gives the benefit of having indexed filters by either typeofdata or importstatus.
Steps To ReproduceIn a big customer instance with import entry mechanism working execute the following sentence in the database:

select * from pg_stat_statements where query ilike '%c_import_entry%typeofdata%';

This will show queries that have been executed in the database trying to filter by typeofdata they will have done it going through the index (typeofdata, created) and afterwards filtering by typeofdata.

Plan without index:
 Limit (cost=2.52..2.52 rows=1 width=1407) (actual time=0.024..0.024 rows=0 loops=1)
   -> Sort (cost=2.52..2.52 rows=1 width=1407) (actual time=0.022..0.022 rows=0 loops=1)
         Sort Key: created, createdtimestamp
         Sort Method: quicksort Memory: 25kB
         -> Index Scan using c_import_entry_idx on c_import_entry c_import_e0_ (cost=0.29..2.51 rows=1 width=1407) (actual time=0.018..0.019 rows=0 loops=1)
               Index Cond: ((importstatus)::text = 'Initial'::text)
               Filter: ((typeofdata)::text = 'Order'::text)
               Rows Removed by Filter: 1

Plan with index:
 Limit (cost=2.51..2.52 rows=1 width=1407) (actual time=0.020..0.020 rows=0 loops=1)
   -> Sort (cost=2.51..2.52 rows=1 width=1407) (actual time=0.019..0.019 rows=0 loops=1)
         Sort Key: created, createdtimestamp
         Sort Method: quicksort Memory: 25kB
         -> Index Scan using c_import_entry_type_status on c_import_entry c_import_e0_ (cost=0.29..2.50 rows=1 width=1407) (actual time=0.015..0.015 rows=0 loops=1)
               Index Cond: (((typeofdata)::text = 'Order'::text) AND ((importstatus)::text = 'Initial'::text))
Proposed Solution
Additional Information
TagsNOR, Performance
Relationships
Attached Filesdiff index.diff (801) 2019-11-25 16:53
https://issues.openbravo.com/file_download.php?file_id=13584&type=bug
Issue History
Date ModifiedUsernameFieldChange
2019-11-25 16:48ioritzCiaNew Issue
2019-11-25 16:48ioritzCiaAssigned To => platform
2019-11-25 16:48ioritzCiaOBNetwork customer => Gold
2019-11-25 16:48ioritzCiaWeb browser => Google Chrome
2019-11-25 16:48ioritzCiaModules => Core
2019-11-25 16:48ioritzCiaTriggers an Emergency Pack => No
2019-11-25 16:53ioritzCiaFile Added: index.diff
2019-11-25 16:57ioritzCiaTag Attached: Performance
2019-12-26 11:07AugustoMauchNote Added: 0116529
2019-12-26 11:07AugustoMauchStatusnew => feedback
2020-08-10 17:15ALopeteguiNote Added: 0121954
2020-08-10 17:21ALopeteguiWeb browserGoogle Chrome => Google Chrome
2020-08-10 17:21ALopeteguiResolution time => 1598220000
2020-08-10 17:21ALopeteguiStatusfeedback => new
2020-08-10 17:23ALopeteguiIssue Monitored: ALopetegui
2020-08-17 12:25AugustoMauchAssigned Toplatform => cberner
2020-08-19 12:07hgbotMerge Request Status => open
2020-08-19 12:07hgbotNote Added: 0122167
2020-08-19 13:28cbernerNote Added: 0122175
2020-08-19 13:28cbernerStatusnew => feedback
2020-08-20 08:38cbernerStatusfeedback => scheduled
2020-08-20 12:34cbernerStatusscheduled => feedback
2020-08-24 13:55ioritzCiaWeb browserGoogle Chrome => Google Chrome
2020-08-24 13:55ioritzCiaSeveritymajor => minor
2020-08-24 13:59AugustoMauchWeb browserGoogle Chrome => Google Chrome
2020-08-24 13:59AugustoMauchResolution time1598220000 =>
2020-09-09 10:21cbernerStatusfeedback => new
2020-10-13 09:48cbernerNote Added: 0123664
2020-10-13 10:10cbernerNote Edited: 0123664bug_revision_view_page.php?bugnote_id=0123664#r21775
2020-10-13 10:10cbernerNote Edited: 0123664bug_revision_view_page.php?bugnote_id=0123664#r21776
2020-10-13 17:09cbernerNote Edited: 0123664bug_revision_view_page.php?bugnote_id=0123664#r21777
2020-11-04 14:28hgbotMerge Request Statusopen => approved
2020-11-04 14:28hgbotResolutionopen => fixed
2020-11-04 14:28hgbotStatusnew => closed
2020-11-04 14:28hgbotNote Added: 0124175
2020-11-04 14:28hgbotFixed in Version => PR21Q1
2020-11-04 14:28hgbotNote Added: 0124176
2021-02-09 10:50rafarodaTag Attached: NOR

Notes
(0116529)
AugustoMauch   
2019-12-26 11:07   
Hello Ioritz,

In the example you wrote in the steps to reproduce it seems there is not much difference in cost (2.52..2.52 vs 2.51..2.52) nor in actual time (0.024..0.024 vs 0.020..0.020).

That small improvement would not make a new index worth it. Do you have any other example?
(0121954)
ALopetegui   
2020-08-10 17:15   
In 20q1 jmeter test, we have detected again this query as the most reading query.
In the example above the shared hit value is missing, and that is the most important value regarding reads.

Wihout index:
 Limit (cost=6805.60..6805.60 rows=2 width=922) (actual time=11.679..11.679 rows=0 loops=1)
   Buffers: shared hit=4088
   -> Sort (cost=6805.60..6805.60 rows=2 width=922) (actual time=11.678..11.678 rows=0 loops=1)
         Sort Key: created, createdtimestamp
         Sort Method: quicksort Memory: 25kB
         Buffers: shared hit=4088
         -> Bitmap Heap Scan on c_import_entry c_import_e0_ (cost=1489.22..6805.59 rows=2 width=922) (actual time=11.672..11.672 rows=0 loops=1)
               Recheck Cond: ((importstatus)::text = 'Initial'::text)
               Filter: ((typeofdata)::text = 'BusinessPartner'::text)
               Rows Removed by Filter: 4841
               Heap Blocks: exact=3880
               Buffers: shared hit=4088
               -> Bitmap Index Scan on c_import_entry_idx (cost=0.00..1489.22 rows=7842 width=0) (actual time=2.150..2.150 rows=27961 loops=1)
                     Index Cond: ((importstatus)::text = 'Initial'::text)
                     Buffers: shared hit=208
 Planning Time: 0.097 ms
 Execution Time: 11.711 ms
(17 rows)

With Index:
 Limit (cost=2.52..2.52 rows=1 width=922) (actual time=0.014..0.014 rows=0 loops=1)
   Buffers: shared hit=2
   -> Sort (cost=2.52..2.52 rows=1 width=922) (actual time=0.013..0.013 rows=0 loops=1)
         Sort Key: created, createdtimestamp
         Sort Method: quicksort Memory: 25kB
         Buffers: shared hit=2
         -> Bitmap Heap Scan on c_import_entry c_import_e0_ (cost=1.39..2.51 rows=1 width=922) (actual time=0.009..0.009 rows=0 loops=1)
               Recheck Cond: (((typeofdata)::text = 'BusinessPartner'::text) AND ((importstatus)::text = 'Initial'::text))
               Buffers: shared hit=2
               -> Bitmap Index Scan on c_import_entry_type_status (cost=0.00..1.39 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1)
                     Index Cond: (((typeofdata)::text = 'BusinessPartner'::text) AND ((importstatus)::text = 'Initial'::text))
                     Buffers: shared hit=2
 Planning Time: 0.162 ms
 Execution Time: 0.039 ms
(14 rows)

As you can see, in a c_import_entry with 5000 rows the shared hit in the first plan is 4088, while with index is only 2.

That index will reduce drastically the reads.
(0122167)
hgbot   
2020-08-19 12:07   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/123 [^]
(0122175)
cberner   
2020-08-19 13:28   
We're not able to reproduce the problematic query(tested on instances with 500.000 registers but was very fast(less than a second)), could you provide us with an instance to test this?

If it is not possible to provide an instance, maybe a test dataset with data that causes the issue.

Thank you
(0123664)
cberner   
2020-10-13 09:48   
(edited on: 2020-10-13 17:09)
Extensive testing has been done in BUT with this index created on both columns. There's a query that shows up very frecuently:
select * from c_import_entry where typeofdata='Order' and importstatus='Initial';

But as sometimes Error/processed requests could happen, the importstatus may change. We think that an index over both typeofdata and importstatus is necessary. Creating an index over typeofdata alone is not enough, as it is not considered in all cases.
(0124175)
hgbot   
2020-11-04 14:28   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/123 [^]
(0124176)
hgbot   
2020-11-04 14:28   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: a8242965a70bc5be6fd1cbd700acf65a400fa8fd
Author: Cristian Berner <cristian.berner@openbravo.com>
Date: 2020-11-04T13:27:26+00:00
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/a8242965a70bc5be6fd1cbd700acf65a400fa8fd [^]

Fixes ISSUE-42392: Create index to improve queries in C_IMPORT_ENTRY

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