Brimbox Logo Brimbox Version 2.3.4 Released

Creating Related Indexes>>

When using related layouts, defined in columns c41 to c46, performance can be improved by placing a special index on the column in a layout. If the Postgres bb_key function is used when creating an index on a related column the partial index will be used when invoking that same function in a query.

Use something like this to create an index on a partial table.

CREATE INDEX data_table_related_idx_c41 ON data_table (cast(bb_key(c41) as bigint)) WHERE row_type = 1

To test whether the index is working use something like this.

SET enable_seqscan = off;

Then explain the statement.

EXPLAIN SELECT * FROM data_table T1 INNER JOIN data_table T2 ON bb_key(T1.c41) =
WHERE T1.row_type = 1 AND T2.row_type = 3 AND bb_key(T1.c41) IS NOT NULL;

Updated: 2016-09-29