Skip to main content
You can use the information_schema.indexes view to return information about each index in a database. The view is available for each database and contains one row for each index in the database. You can use a SELECT query to return information about each index. In order to view index information, you need the USAGE privilege on both the schema and the database. You also need ownership of the table or the necessary table-level privileges required for the intended action. The following query returns all aggregating indexes defined within the current database. 
SELECT
  *
FROM
  information_schema.indexes
WHERE
  index_type='aggregating`;

​
Columns in information_schema.indexes

Each row has the following columns with information about the database.
Column NameData TypeDescription
table_catalogTEXTName of the catalog. Firebolt provides a single ‘default’ catalog.
table_schemaTEXTName of the database.
table_nameTEXTThe name of the table for which the index is defined.
index_nameTEXTThe name defined for the index.
index_typeTEXTOne of either primary or aggregating.
index_ownerTEXTThe owner of the table, which is the owner of the index.
index_originTEXTThe origin where this index is from. USER if explicitly created by the user, SYSTEM if implicitly created by the system as a byproduct of another statement, or NULL if not applicable.
index_definitionTEXTThe part of the index statement that specifies the columns and any aggregations included in the index.
compressed_bytesBIGINTThe compressed size of the index, in bytes.
uncompressed_bytesBIGINTThe uncompressed size of the index, in bytes.
number_of_tabletsBIGINTThe number of tablets in the index.
createdTIMESTAMPTZTime that the index was created.
⌘I