Brimbox Tuning and Indexes>>
Being built on top of the full featured PostgreSQL database allows additional indexing on a Brimbox data table through the use of partial indexes. However, before adding partial indexes to Brimbox, there should be an understanding of the indexes built into a default Brimbox data table. They are:
- id – A primary key on the serial id (autoincrement) column.
- row_type – An index on the row_type column significantly improves query execution, almost all Brimbox queries use
- key1 – an index is necessary on the primary column used in joining one psuedo-table (or layout) to another.
- fts, ftg – There are gin indexes on the full text columns.
One of the issues found in early instances of Brimbox was slow rendering during reporting processes. This was mainly due to DISTINCT queries used for report query parameters. An example would be using a list of chapters for an organization to define report results. Running several queries like the one below was slowing down execution.
SELECT DISTINCT c03 FROM data_table WHERE row_type = 2;
Since column c03 probably holds other information in other row_type(s) defining layout, it probably wouldn’t be prudent to throw a full index on column c03. However, since Postgres is full featured we can implement a partial index.
CREATE INDEX data_table_rt2_c03_idx ON data_table (c03) WHERE row_type = 2;
This will make the query above run much faster. Using a partial index based on row_type is very similar to putting a full index on a table in a regular multi-table layout. So the one data table layout of Brimbox does not hamper the use of indexes for database tuning.