View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

Projects:Support Partial Indexes On Nullable Columns/QA

Code review: https://docs.google.com/spreadsheets/d/1IeErpAc8vUzsLIDpizsQrXjoKgfXFf4ziyHOHcatA7I/edit#gid=0

Automatic Tests

Along the development of this process, the Test Driven Development approach has been used. As result, a set of JUnit test cases were created and included within the DBSM Test Suite.

The tests which cover the partial index behavior can be found here and with them we are testing what follows:

Besides, a refactor has been done to avoid code duplication and a new base class has been created, IndexBaseTest. The new test class called PartialIndexes, created for this project together with the previously existing classes for index testing (FunctionBasedIndexes and OperatorClassIndexes) extends the IndexBaseTest class.

Finally another test case, ConfigScriptSuite, was created and included into the existing ConfigScriptSuite. This test case was designed to cover the ability of exporting the removal of an index into a configuration script.

Performance Tests

In order to measure the performance of a partial index, we have used an Openbravo environment with 19.154.902 business partners on a PostgresSQL database.

Within this environment all of the business partners has a NULL value for the AD_FORCED_ORG_ID column. Therefore we are going to compare different metrics of the following two indexes:

BASIC INDEX

 
CREATE INDEX C_BPARTNER_ADLANGUAGE ON C_BPARTNER (AD_FORCED_ORG_ID)

PARTIAL INDEX

 
CREATE INDEX C_BPARTNER_ADLANGUAGE ON C_BPARTNER (AD_FORCED_ORG_ID) WHERE AD_FORCED_ORG_ID IS NOT NULL

And these are the select queries executed for the testings:

QUERY 1

 
SELECT count(*) FROM C_BPARTNER WHERE AD_FORCED_ORG_ID IS NOT NULL;

QUERY 2

 
SELECT count(*) FROM C_BPARTNER WHERE AD_FORCED_ORG_ID IS NULL;

The results are summarized in the following table:


INDEX TYPE INDEX CREATION TIME (ms) INDEX SIZE (bytes) INSERT RECORD TIME (ms) DELETE RECORD TIME (ms) QUERY 1 TIME (ms) QUERY 2 TIME (ms)
NO INDEX -- -- 19.200 289310.489 3208.748 4064.957
BASIC 8703 410000000 6.941 305977.447 0.371 4107.460
PARTIAL 4176 8192 5.810 265313.110 0.468 5137.448


In terms of query execution times the basic and the partial index achieve similar results, improving on the case without index. But with the partial index we are saving a 99,99% of the disk space used to store the index information.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Support_Partial_Indexes_On_Nullable_Columns/QA"

This page has been accessed 174 times. This page was last modified on 30 November 2016, at 16:15. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.