Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0042392
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformminoralways2019-11-25 16:482021-02-09 10:50
ReporterioritzCiaView Statuspublic 
Assigned Tocberner 
PriorityhighResolutionfixedFixed in VersionPR21Q1
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget Version
OSLinux 64 bitDatabasePostgreSQLJava version8
OS VersionUbuntu 18.04Database version11Ant version1.9
Product VersionSCM revision 
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
Summary

0042392: 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))
TagsNOR, Performance
Attached Filesdiff file icon index.diff [^] (801 bytes) 2019-11-25 16:53 [Show Content]

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0116529)
AugustoMauch (administrator)
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 (viewer)
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 (developer)
2020-08-19 12:07

 Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/123 [^]
(0122175)
cberner (viewer)
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 (viewer)
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 (developer)
2020-11-04 14:28

 Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/123 [^]
(0124176)
hgbot (developer)
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
---

- Issue History
Date Modified Username Field Change
2019-11-25 16:48 ioritzCia New Issue
2019-11-25 16:48 ioritzCia Assigned To => platform
2019-11-25 16:48 ioritzCia OBNetwork customer => Gold
2019-11-25 16:48 ioritzCia Web browser => Google Chrome
2019-11-25 16:48 ioritzCia Modules => Core
2019-11-25 16:48 ioritzCia Triggers an Emergency Pack => No
2019-11-25 16:53 ioritzCia File Added: index.diff
2019-11-25 16:57 ioritzCia Tag Attached: Performance
2019-12-26 11:07 AugustoMauch Note Added: 0116529
2019-12-26 11:07 AugustoMauch Status new => feedback
2020-08-10 17:15 ALopetegui Note Added: 0121954
2020-08-10 17:21 ALopetegui Web browser Google Chrome => Google Chrome
2020-08-10 17:21 ALopetegui Resolution time => 1598220000
2020-08-10 17:21 ALopetegui Status feedback => new
2020-08-10 17:23 ALopetegui Issue Monitored: ALopetegui
2020-08-17 12:25 AugustoMauch Assigned To platform => cberner
2020-08-19 12:07 hgbot Merge Request Status => open
2020-08-19 12:07 hgbot Note Added: 0122167
2020-08-19 13:28 cberner Note Added: 0122175
2020-08-19 13:28 cberner Status new => feedback
2020-08-20 08:38 cberner Status feedback => scheduled
2020-08-20 12:34 cberner Status scheduled => feedback
2020-08-24 13:55 ioritzCia Web browser Google Chrome => Google Chrome
2020-08-24 13:55 ioritzCia Severity major => minor
2020-08-24 13:59 AugustoMauch Web browser Google Chrome => Google Chrome
2020-08-24 13:59 AugustoMauch Resolution time 1598220000 =>
2020-09-09 10:21 cberner Status feedback => new
2020-10-13 09:48 cberner Note Added: 0123664
2020-10-13 10:10 cberner Note Edited: 0123664 View Revisions
2020-10-13 10:10 cberner Note Edited: 0123664 View Revisions
2020-10-13 17:09 cberner Note Edited: 0123664 View Revisions
2020-11-04 14:28 hgbot Merge Request Status open => approved
2020-11-04 14:28 hgbot Resolution open => fixed
2020-11-04 14:28 hgbot Status new => closed
2020-11-04 14:28 hgbot Note Added: 0124175
2020-11-04 14:28 hgbot Fixed in Version => PR21Q1
2020-11-04 14:28 hgbot Note Added: 0124176
2021-02-09 10:50 rafaroda Tag Attached: NOR

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker