View source | View content page | Page history | Printable version   

Projects:Support Partial Indexes On Nullable Columns/QA

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_AD_FORCED_ORG ON C_BPARTNER (AD_FORCED_ORG_ID)

PARTIAL INDEX

 
CREATE INDEX C_BPARTNER_AD_FORCED_ORG 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 3532.557 4657.884
BASIC 8703 410000000 6.941 305977.447 1.290 2017.344
PARTIAL 4176 8192 5.810 265313.110 0.850 4646.928


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.

Code review

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

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

This page has been accessed 1,144 times. This page was last modified on 21 October 2019, at 16:14. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.