Indexes
An index is used to improve the performance of data access within the system.
There are four types of indexes:
Primary Key Index
A primary key index is created by default when a table is
created with a primary key specified. It will match the
primary key in nature, in that it will be a single-column index if the
primary key is on a single column and a multi-column composite index if the
primary key is a composite primary key.
The primary key index is hash-based and optimizes the performance of
equality-based filter expressions (e.g. (x = 0)). Note that if the index
is a composite index, the filter expressions employed must reference all
of the primary key columns to take advantage of the performance gains of the
index.
Operations which make use of a primary key index include:
Relational Index
A relational index is created as the result of applying a
foreign key to a column. A description of the index and
its requirements can be found under Foreign Keys.
Column Index
A column index (also known as an attribute index) can be applied to a column
in a table to improve the performance of operations applied to that
column in an expression.
The column index is implemented as a b-tree, which provides performance
improvements for both equality-based (e.g. (x = 0)) and range-based (e.g.
(x > 0)) filter criteria on individual columns. Multi-column and
function-based indexes are not supported at this time.
As primary key indexes only optimize the performance of equality-based filter
criteria, a column index can be applied to a primary key column (or each of
the columns in a composite primary key) for a performance boost on range-based
filter criteria--the two index types are not mutually exclusive.
Column indexes optimize the performance of the following operations, when
those operations are given equality-based or range-based filter expressions:
A column index will additionally improve the performance of the following
operations:
To apply a column index to a column, the /alter/table endpoint
should be called with the create_index action. The default index type is
the column index, so no index_type option is necessary to specify.
For example, in Python:
retobj = h_db.alter_table(
table_name = "indexed_table",
action = 'create_index',
value = 'id_a'
)
Limitations
- A column index can be applied to any table column without:
- A column index can be applied to tables as well as to
materialized views that do not have a filter or
join as the last operation in their creation.
Chunk Skip Index
A chunk skip index can be applied to a column in a table to improve the
performance of equality-based filtering expressions applied to that column. The
primary use for this index is with partitioned tables,
where the filter is applied to one or more of the partition key columns. The
larger the data set and the greater the number of partitions generated, the more
performant filters on the table will be when the index is applied.
To apply a chunk skip index to a column, the /alter/table endpoint
should be called with the create_index action and the chunk_skip index
type.
For example, in Python:
retobj = h_db.alter_table(
table_name = "indexed_table",
action = 'create_index',
value = 'id_b',
options = {'index_type': 'chunk_skip'}
)
Limitations
- A chunk skip index can be applied to any table column without:
- A chunk skip index can be applied to tables as well as to
materialized views that do not have a filter or
join as the last operation in their creation.
