Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
0032211Openbravo ERPY. DBSourceManagerpublic2016-02-11 17:492016-12-16 18:38
alostale 
caristu 
normalminorN/A
closedfixed 
5
 
3.0PR17Q1 
alostale
Core
No
0032211: support for partial indexes
From 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 [^]
In 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
No tags attached.
related to design defect 0034037 closed platform Is not possible to export the removal of an index into a configuration script 
related to feature request 0034138 closed caristu Create a Test Suite for Configuration Scripts 
related to defect 0034236 closed caristu newIndexesPostAction is executing duplicated actions 
related to design defect 0034258 new platform Not possible to apply two indexes on the same column but with different operator class in Oracle 
related to defect 0034646 closed caristu Comments should be queried from user tables 
related to defect 0035370 closed caristu Partial Index information (comments) is not applied properly under some circumnstances 
related to defect 0040933 closed AugustoMauch Export database changes Index order if a template removes it 
blocks defect 0032205 closed collazoandy4 Deleting a line on the order line window takes long on environment with big amount of data 
blocks feature request 00352043.0PR17Q2 closed inigosanchez define partial indexes for not null values 
blocks feature request 0035205 closed vmromanos define partial indexes for not null values 
blocks feature request 0035206 closed marvintm define partial indexes for not null values 
Issue History
2016-02-11 17:49alostaleNew Issue
2016-02-11 17:49alostaleAssigned To => platform
2016-02-11 17:49alostaleModules => Core
2016-02-11 17:49alostaleTriggers an Emergency Pack => No
2016-02-11 17:50alostaleRelationship addedrelated to 0032205
2016-02-11 17:55shuehnerIssue Monitored: shuehner
2016-03-04 10:45aferrazRelationship deletedrelated to 0032205
2016-03-04 10:45aferrazRelationship addedblocks 0032205
2016-09-20 13:33caristuRelationship addedrelated to 0034037
2016-09-29 19:27caristuRelationship addedrelated to 0034138
2016-10-19 10:28caristuRelationship addedrelated to 0034236
2016-10-20 19:01caristuRelationship addedrelated to 0034258
2016-11-08 19:10caristuAssigned Toplatform => caristu
2016-11-08 19:37hgbotCheckin
2016-11-08 19:37hgbotNote Added: 0091256
2016-11-08 19:37hgbotStatusnew => resolved
2016-11-08 19:37hgbotResolutionopen => fixed
2016-11-08 19:37hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/7e7863155165f25f06e634bdab3f47c28f0f5231 [^]
2016-11-11 09:08alostaleReview Assigned To => alostale
2016-11-11 09:10alostaleNote Added: 0091373
2016-11-11 09:10alostaleStatusresolved => closed
2016-11-11 09:10alostaleFixed in Version => 3.0PR17Q1
2016-11-28 12:13caristuRelationship addedrelated to 0034646
2016-12-16 18:38hudsonbotCheckin
2016-12-16 18:38hudsonbotNote Added: 0092601
2017-02-08 16:42alostaleRelationship addedblocks 0035204
2017-02-08 16:44alostaleRelationship addedblocks 0035205
2017-02-08 16:45alostaleRelationship addedblocks 0035206
2017-02-27 18:10caristuRelationship addedrelated to 0035370
2019-05-27 17:38caristuRelationship addedrelated to 0040933

Notes
(0091256)
hgbot   
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   
2016-11-11 09:10   
code reviewed and tested within project: http://wiki.openbravo.com/wiki/Projects:Support_Partial_Indexes [^]
(0092601)
hudsonbot   
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