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:
Column Index
A column index can be applied to a column in a table or view 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 option. For example, in Python:
retobj = gpudb.alter_table(
table_name = '<name_of_table>',
action = 'create_index'
value = '<name_of_column>'
)
Limitations
- A column index can be applied to any column without a
store-only designation.
- A column index can be applied to tables and most
views, with the exception of filtered views & join views.
