SQL CREATE INDEX

An SQL index, which corresponds to an underlying eXtremeDB index, can be created to improve lookup performance for specific queries. (Please see SQL Optimizer for details about performance optimization using indexes.) Indexes are created using the SQL CREATE INDEX statement, specifying the table and key field(s). For example:

    CREATE TABLE t (x INTEGER);
    CREATE INDEX tx ON t(x);

Here a B-Tree (tree) index (the default type) is created on column (field) x of table t.

The syntax is as follows:

    CREATE [UNIQUE] INDEX name ON table ( key { , key } )
        [USING (HASH | RTREE | PTREE | TRIGRAM | HNSW | INCLUSIVE)]
        [IF NOT EXISTS]

    key : column_name [ ASC | DESC ]

Note that all SQL keywords are case insensitive in eXtremeSQL — i.e., CREATE INDEX and create index are equivalent.

The keywords HASH, RTREE, PTREE, and TRIGRAM refer to the eXtremeDB indexes of type hash (hash table), rtree (spatial search), trie (Patricia Trie), trigram (Trigram search), HNSW (HNSW search), respectively. The keyword INCLUSIVE indicates that the index is a key-value-inclusive index.

A key specification can include the keywords ASC or DESC to indicate whether the index sorts in ascending or descending order. Note that compound indexes can be created by specifying a comma-delimited list of keys.

IF NOT EXISTS

The IF NOT EXISTS clause can be used to override an error when the index already exists. For example, note how the following CREATE INDEX statement fails, while adding the IF NOT EXISTS clause allows the execution to succeed:

    XSQL> CREATE TABLE foo(x INTEGER);
    XSQL> INSERT INTO foo VALUES (1);
    XSQL> CREATE INDEX idx ON foo(x);
    XSQL> CREATE INDEX idx ON foo(x);
    ERROR: Compiler error at position 24: Index idx already defined for table foo
    CREATE INDEX idx ON foo(x)
    ^
    XSQL> CREATE INDEX IF NOT EXISTS idx ON foo(x);
    XSQL> SELECT * FROM foo;
    x
    ------------------------------------------------------------------------------
    1

    Selected records: 1

Parameters for HNSW and Vamana Indexes

SQL can create and drop ANN indexes dynamically. You can override the default configuration parameters for HNSW and Vamana indexes to tune performance, memory usage, and search accuracy. These parameters are set using the SET command in the SQL interface or via the corresponding API calls.

For example, to configure an HNSW index with specific distance metrics and graph connectivity:

    SET hnsw_dist = l2;
    SET hnsw_m = 16;
    SET ef_construction = 64;
    SET ef_search = 32;

    create table embeddings (
        id integer primary key,
        name string,
        embedding array(float, 3)
    );

    create index embedding_ann_ix
        on embeddings(embedding)
        using hnsw;

    select id, name
    from embeddings
    where embedding near [0.11, 0.10, 0.10]
    limit 3;    

The query-time search width `ef_search` is read when a search cursor/query is started. It can be tuned without rebuilding the index.

Distance Metric (hnsw_dist)

The hnsw_dist parameter defines the distance metric used to calculate similarity between vectors. Supported values include:

• l2 — Euclidean distance (L2 norm). Recommended for most general-purpose vector searches.
• ip or inner_product — Dot product. Often used when vectors are normalized (equivalent to cosine similarity in that case).
• cos or cosine — Cosine distance. Measures the angle between vectors, ignoring their magnitude.
• manhattan — Manhattan distance (L1 norm). Useful for high-dimensional sparse data.

Graph Connectivity and Search Depth

• hnsw_m (or m): The maximum number of connections (edges) per node in the graph. Higher values increase recall and search speed but significantly increase memory consumption and indexing time. Typical values range from 8 to 64.
• ef_construction: The size of the dynamic candidate list during index construction. Higher values result in a higher-quality graph (better recall) but slower indexing. Must be greater than or equal to m.
• ef_search (or ef): The size of the dynamic candidate list during search. Higher values increase search accuracy (recall) but increase latency. This parameter can be adjusted at query time without reindexing.

Switching to Vamana Index

The Vamana index (also known as DiskANN) is a variant optimized for different trade-offs between memory and speed. To enable the Vamana algorithm instead of the standard HNSW, you must specify the vamana_alpha parameter with a value greater than zero.

The vamana_alpha parameter controls the aggressiveness of the graph pruning process. It is specified as an integer equal to the actual floating-point alpha multiplied by 100 (e.g., 100 corresponds to alpha = 1.0, and 120 corresponds to alpha = 1.2). For example:

    set vamana_alpha = 120;
    set vamana_build_method = medoid;

    create index vamana_embedding_ix
        on embeddings(embedding)
        using hnsw;

Note: When vamana_alpha is set, the index construction algorithm switches to the Vamana method. Ensure that other parameters (such as m and ef_construction) are tuned appropriately for Vamana, as optimal values may differ from those used for HNSW.

Build Method (vamana_build_method)

The vamana_build_method parameter controls how the starting point (root) is chosen during Vamana graph construction. It accepts the following values:

• "incremental" (or "default") — The first inserted point becomes the root. This is the default behavior.
• "medoid" — The root point is selected via a two-pass process based on the existing values in the table.
• "centroid" — An artificial central point, computed as the mean of all coordinates, is created and used as the anchor vertex for graph construction.

For more information about HNSW and Vamana indexes, please refer to the HNSW and Vamana Indexes page.