Create an Index on your table

Create an index sorted in descending order:

CREATE INDEX idx_products_price_desc ON myschema.products (price DESC);

Here are many more examples (note that best practices for index creation are in the "best practices" section of this page):

1. Basic Index (Default BTREE with multiple columns)

CREATE INDEX BOAT_IDX ON NORTH.BOAT (ID, COST)

• No explicit type specified, defaults to BTREE
• Multi-column index

2. BTREE Index (Explicit)

CREATE INDEX BOAT_IDX_BTREE ON NORTH.BOAT USING BTREE (ID)

• Explicitly specifies BTREE type
• Single column index

3. HASH Index

CREATE INDEX BOAT_IDX_HASH ON NORTH.BOAT USING HASH (NAME)

• Uses HASH algorithm
• Good for equality comparisons

4. GIN Index (Generalized Inverted Index)

CREATE INDEX BOAT_IDX_GIN ON NORTH.BOAT USING GIN (NAME)

• Used for composite values (arrays, full-text search, JSONB)

5. GIST Index (Generalized Search Tree)

CREATE INDEX BOAT_IDX_GIST ON NORTH.BOAT USING GIST (NAME)

• Used for geometric data, full-text search, range types

Drop an Index

Remove an existing index:

DROP INDEX myschema.idx_users_email;

Drop an index only if it exists:

DROP INDEX IF EXISTS myschema.idx_users_email;

Limitations

1. Maximum Indexes per Subscription – Although adding indexes is free (since only query compute is metered not storage), each subscription is limited to 10 indexes by default.
2. External Tables – Indexes cannot be created on external tables or materialized views.
3. System Schemas – Indexes cannot be created on system schemas such as SXT_META, SYS, etc.
4. Index Name Length – Index names must not exceed 63 characters.
5. Table Requirement – The referenced table must exist before creating an index.

Best Practices for adding Indexes

1. Index Selective Columns – Use indexes on columns in WHERE, JOIN, ORDER BY clauses.
2. Composite Indexes – More efficient than multiple single-column indexes.
3. Column Order Matters – Put most selective column first.
4. Avoid Over-Indexing – Too many indexes slow writes.
5. Choose Right Index Type:
   1. BTREE – Range queries and sorting
   2. HASH – Equality lookups
   3. GIN – Arrays and full-text search

Error Messages

"Maximum number of indexes reached" – Drop unused indexes for your Subscription or contact support.

"Indexes cannot be created on external tables" – Use native PostgreSQL tables.

"Index already exists" – Use different name for the index or drop an existing index if you've already created one with the same name.

"Table not found" – Verify schema and table name then try again.