Primary index overview
CREATE TABLE
statement:
Parameter | Description |
---|---|
table_name | The name of the table where the primary index is applied. |
column_name1, ... | The columns chosen to be included in the primary index. |
index_granularity | The maximum number of rows in each granule. See Index granularity. |
SubmitDate
and EngineName
:
information_schema.tables
to access the fragmentation metric to assess fragmentation levels and determine whether maintenance actions are needed.VACUUM
command to clean up rows flagged for deletion and reorganize fragmented data. It is particularly useful when large numbers of rows have been deleted or updates have introduced significant fragmentation.index_granularity
storage parameter,
specified in the WITH
clause, is an advanced setting that may be useful for improving performance in very specific query patterns.
It defines the maximum number of rows per granule, which directly impacts how data is indexed and queried.
<index_granularity>
must be a power of 2, ranging from 128 to 8192. The default value is 8192. We recommend using the default value, but lower values can decrease query latency by 10x or more in some query patterns.
index_granularity
, which should translate to good performance for most queries. The following workload patterns may benefit from higher or lower values for index_granularity
:
index_granularity
value can reduce unnecessary row scans and improve efficiency. However, this increases static memory usage for storing the index.index_granularity
value is more efficient, as it reduces index memory usage and overhead introduced by each granule boundary.index_granularity
, start with the default value, then create duplicate tables with different settings to compare both the query latency and memory usage.