0032211: support for partial indexes - Openbravo Issue Tracking System

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0032211

Openbravo ERP

Y. DBSourceManager

public

2016-02-11 17:49

2016-12-16 18:38

Reporter

alostale

Assigned To

caristu

Priority

normal

Severity

minor

Reproducibility

N/A

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

Target Version

Fixed in Version

3.0PR17Q1

Merge Request Status

Review Assigned To

alostale

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0032211: support for partial indexes

Description

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 [^]

Steps To Reproduce

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

Proposed Solution

Additional Information

Tags

No tags attached.

Relationships

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	Triage Platform Base	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	0035204	3.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