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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0032211
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Openbravo ERP] Y. DBSourceManagerminorN/A2016-02-11 17:492016-12-16 18:38
ReporteralostaleView Statuspublic 
Assigned Tocaristu 
PrioritynormalResolutionfixedFixed in Version3.0PR17Q1
StatusclosedFix in branchFixed in SCM revision7e7863155165
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Toalostale
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0032211: support for partial indexes

DescriptionFrom PG documentation [1]:

A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries only for those table rows that satisfy the predicate. Partial indexes are a specialized feature, but there are several situations in which they are useful.

One major reason for using a partial index is to avoid indexing common values. Since a query searching for a common value (one that accounts for more than a few percent of all the table rows) will not use the index anyway, there is no point in keeping those rows in the index at all. This reduces the size of the index, which will speed up those queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases

There are cases where these kind of indexes would be very beneficial for Openbravo, ie. indexes described in [2]. In this particular instance, having 12M rows in c_orderline each of the 3 required indexes would consume ~239MB in disk. But most of the rows have null values for these columns, creating a partial index for not null values, would deliver the same improvement in deletion saving a lot of disk space. It would also improve insertion times compared to full indexes.

---
[1] http://www.postgresql.org/docs/current/static/indexes-partial.html [^]
[2] https://issues.openbravo.com/view.php?id=32205#c84127 [^]
Steps To ReproduceIn an instance with 12M rows in c_orderline:
* Disk size for index got reduced from 239MB to 16kB (for this concrete case, where most of the values are null)
* Insertion performance is expected to improve, though it has not been measured

----

CREATE INDEX c_orderline_bom_parent_idx ON c_orderline (bom_parent_id);

select relname, reltuples,pg_size_pretty(pg_relation_size(oid)) from pg_class where relname where relname = 'c_orderline_bom_parent_idx';

            relname | reltuples | pg_size_pretty
-------------------------------+-------------+----------------
 c_orderline_bom_parent_idx | 1.11422e+07 | 239 MB

select count(*) from c_orderline where bom_parent_id is not null;
 count
-------
    55

CREATE INDEX c_orderline_bom_p_partidx on c_orderline (bom_parent_id) where bom_parent_id is not null;

select relname, reltuples,pg_size_pretty(pg_relation_size(oid)) from pg_class where relname where relname = 'c_orderline_bom_p_partidx';

            relname | reltuples | pg_size_pretty
-------------------------------+-------------+----------------
 c_orderline_bom_p_partidx | 55 | 16 kB
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to design defect 0034037 closedplatform Is not possible to export the removal of an index into a configuration script 
related to feature request 0034138 closedcaristu Create a Test Suite for Configuration Scripts 
related to defect 0034236 closedcaristu newIndexesPostAction is executing duplicated actions 
related to design defect 0034258 newTriage Platform Base Not possible to apply two indexes on the same column but with different operator class in Oracle 
related to defect 0034646 closedcaristu Comments should be queried from user tables 
related to defect 0035370 closedcaristu Partial Index information (comments) is not applied properly under some circumnstances 
related to defect 0040933 closedAugustoMauch Export database changes Index order if a template removes it 
blocks defect 0032205 closedcollazoandy4 Deleting a line on the order line window takes long on environment with big amount of data 
blocks feature request 00352043.0PR17Q2 closedinigosanchez define partial indexes for not null values 
blocks feature request 0035205 closedvmromanos define partial indexes for not null values 
blocks feature request 0035206 closedmarvintm define partial indexes for not null values 

-  Notes
(0091256)
hgbot (developer)
2016-11-08 19:37

 Repository: erp/devel/pi
Changeset: 7e7863155165f25f06e634bdab3f47c28f0f5231
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Tue Nov 08 19:34:55 2016 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/7e7863155165f25f06e634bdab3f47c28f0f5231 [^]

fixes issue 32211: support for partial indexes

---
M src-db/database/lib/dbsourcemanager.jar
---
(0091373)
alostale (manager)
2016-11-11 09:10

 code reviewed and tested within project: http://wiki.openbravo.com/wiki/Projects:Support_Partial_Indexes [^]
(0092601)
hudsonbot (developer)
2016-12-16 18:38

 A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/dc8bf00badd0 [^]
Maturity status: Test

- Issue History
Date Modified Username Field Change
2016-02-11 17:49 alostale New Issue
2016-02-11 17:49 alostale Assigned To => platform
2016-02-11 17:49 alostale Modules => Core
2016-02-11 17:49 alostale Triggers an Emergency Pack => No
2016-02-11 17:50 alostale Relationship added related to 0032205
2016-02-11 17:55 shuehner Issue Monitored: shuehner
2016-03-04 10:45 aferraz Relationship deleted related to 0032205
2016-03-04 10:45 aferraz Relationship added blocks 0032205
2016-09-20 13:33 caristu Relationship added related to 0034037
2016-09-29 19:27 caristu Relationship added related to 0034138
2016-10-19 10:28 caristu Relationship added related to 0034236
2016-10-20 19:01 caristu Relationship added related to 0034258
2016-11-08 19:10 caristu Assigned To platform => caristu
2016-11-08 19:37 hgbot Checkin
2016-11-08 19:37 hgbot Note Added: 0091256
2016-11-08 19:37 hgbot Status new => resolved
2016-11-08 19:37 hgbot Resolution open => fixed
2016-11-08 19:37 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7e7863155165f25f06e634bdab3f47c28f0f5231 [^]
2016-11-11 09:08 alostale Review Assigned To => alostale
2016-11-11 09:10 alostale Note Added: 0091373
2016-11-11 09:10 alostale Status resolved => closed
2016-11-11 09:10 alostale Fixed in Version => 3.0PR17Q1
2016-11-28 12:13 caristu Relationship added related to 0034646
2016-12-16 18:38 hudsonbot Checkin
2016-12-16 18:38 hudsonbot Note Added: 0092601
2017-02-08 16:42 alostale Relationship added blocks 0035204
2017-02-08 16:44 alostale Relationship added blocks 0035205
2017-02-08 16:45 alostale Relationship added blocks 0035206
2017-02-27 18:10 caristu Relationship added related to 0035370
2019-05-27 17:38 caristu Relationship added related to 0040933

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker