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.

Tip

For primary key SQL syntax, see CREATE TABLE.

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.

Tip

In SQL, foreign keys can be applied during table creation, via CREATE TABLE, as well as afterwards, via ALTER TABLE.

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.

Tip

In SQL, column indexes can be applied during table creation, via the CREATE TABLE index clause, as well as afterwards, via ALTER TABLE.

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, to create the index:

Python
SQL

Python
1
2
3
4
5
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "dept_id"
)
SQL
1
2
ALTER TABLE example.employee
ADD INDEX (dept_id)

To drop the index:

Python
SQL

Python
1
2
3
4
5
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "delete_index",
    value = "dept_id"
)
SQL
1
2
ALTER TABLE example.employee
DROP INDEX (dept_id)

Limitations

A column index can be applied to any table column effective type except:
- bytes
- wkt
- json
- array of any type
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.

Tip

In SQL, chunk skip indexes can be applied during table creation, via the CREATE TABLE index clause, as well as afterwards, via ALTER TABLE.

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, to create the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "dept_id",
    options = {"index_type": "chunk_skip"}
)
SQL
1
2
ALTER TABLE example.employee
ADD CHUNK SKIP INDEX (dept_id)

To drop the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "delete_index",
    value = "dept_id",
    options = {"index_type": "chunk_skip"}
)
SQL
1
2
ALTER TABLE example.employee
DROP CHUNK SKIP INDEX (dept_id)

Limitations

A chunk skip index can be applied to any table column with an effective type matching any of the following:
- int
- long
- unsigned long
- charN
- date
- time
- datetime
- timestamp
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.

Tip

In SQL, geospatial indexes can be applied during table creation, via the CREATE TABLE index clause, as well as afterwards, via ALTER TABLE.

Geospatial indexes can be applied to either of the following:

a WKT column
two float or double 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, to apply a geospatial index to a WKT column:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "work_district",
    options = {"index_type": "geospatial"}
)
SQL
1
2
ALTER TABLE example.employee
ADD GEOSPATIAL INDEX (work_district)

To drop the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "delete_index",
    value = "work_district",
    options = {"index_type": "geospatial"}
)
SQL
1
2
ALTER TABLE example.employee
DROP GEOSPATIAL INDEX (work_district)

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

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "office_longitude,office_latitude",
    options = {"index_type": "geospatial"}
)
SQL
1
2
ALTER TABLE example.employee
ADD GEOSPATIAL INDEX (office_longitude, office_latitude)

To drop the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "delete_index",
    value = "office_longitude,office_latitude",
    options = {"index_type": "geospatial"}
)
SQL
1
2
ALTER TABLE example.employee
DROP GEOSPATIAL INDEX (office_longitude, office_latitude)

CAGRA Index

A CAGRA index can be applied to a vector column in a table to improve the performance of vector searches applied to that column.

Tip

In SQL, CAGRA indexes can be applied during table creation, via the CREATE TABLE index clause, as well as afterwards, via ALTER TABLE.

Two additional index options are available for CAGRA indexes:

graph_build_algo - the approximate nearest neighbor algorithm to use in building the index's graph
itopk_size - the size of the list of nodes to traverse across search iterations; the higher the number, the greater the accuracy of the results, but the longer the search will take

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

For example, to create the index:

Python
SQL

Python
1
2
3
4
5
6
7
8
9
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "create_index",
    value = "profile",
    options = {
            "index_type": "cagra",
            "index_options": "itopk_size: 128, graph_build_algo: ivf_pq"
    }
)
SQL
1
2
3
ALTER TABLE example.employee
ADD CAGRA INDEX (profile)
WITH OPTIONS (index_options = 'itopk_size: 128, graph_build_algo: ivf_pq')

To refresh the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "refresh_index",
    value = "profile",
    options = {"index_type": "cagra"}
)
SQL
1
2
ALTER TABLE example.employee
REFRESH CAGRA INDEX (profile)

To drop the index:

Python
SQL

Python
1
2
3
4
5
6
retobj = h_db.alter_table(
    table_name = "example.employee",
    action = "delete_index",
    value = "profile",
    options = {"index_type": "cagra"}
)
SQL
1
2
ALTER TABLE example.employee
DROP CAGRA INDEX (profile)