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:

A partial index can be imported/exported properly.
A partial index which make use of a function can be imported/exported properly.
A basic index can be changed as partial (by recreating it) and vice versa.
In Oracle partial indexes are not supported but the column comments created to keep consistency between the XML model and the database must be created/deleted properly.
If an index is changed as partial, that index is recreated in PostgreSQL but not in Oracle.
If a partial index is changed as not partial, that index is recreated in PostgreSQL but not in Oracle.
If a partial index makes use of an expression with whitespaces inside quotes, those whitespaces are not removed.
The column comments created for on create default statements must be compatible with the comments for partial indexes.

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.