Indexes

An index is used to improve the performance of data access within the system.

There are several 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:

1
2
3
4
5

retobj = h_db.alter_table(
    table_name = "example.indexed_table",
    action = 'create_index',
    value = 'id_a'
)

Limitations

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:

1
2
3
4
5
6

retobj = h_db.alter_table(
    table_name = "example.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.

Geospatial Index

A geospatial index can be applied to one or more columns in a table to improve the performance of geospatial functions applied to them.

Geospatial indexes can be applied to either of the following:

  • a WKT column
  • two columns that constitute a coordinate pair

The performance of the following functions can be improved with the use of a geospatial index:

Scalar Functions Enhanced Performance Scalar Functions
ST_CONTAINS STXY_CONTAINS
ST_CONTAINS_PROPERLY STXY_CONTAINS_PROPERLY
ST_COVERED_BY STXY_COVERED_BY
ST_COVERS STXY_COVERS
ST_CROSSES  
ST_DWITHIN STXY_DWITHIN
ST_ENV_DWITHIN STXY_ENV_DWITHIN
ST_ENV_INTERSECTS STXY_ENV_INTERSECTS
ST_INTERSECTS STXY_INTERSECTS
ST_OVERLAPS  
ST_TOUCHES STXY_TOUCHES
ST_WITHIN STXY_WITHIN

To apply a geospatial index to a column, the /alter/table endpoint should be called with the create_index action and the geospatial index type.

For example, in Python, to apply a geospatial index to a WKT column:

1
2
3
4
5
6

retobj = h_db.alter_table(
    table_name = "example.indexed_table",
    action = 'create_index',
    value = 'wkt',
    options = {'index_type': 'geospatial'}
)

To apply a geospatial index to a coordinate pair of columns:

1
2
3
4
5
6

retobj = h_db.alter_table(
    table_name = "example.indexed_table",
    action = 'create_index',
    value = 'x,y',
    options = {'index_type': 'geospatial'}
)