Hologres:HGraph index guide (Recommended)

Create an index

Syntax: Create a vector index when you create a table.

Description: In Hologres, a vector is represented by a float4 array. The vector dimension is represented by the length of the one-dimensional array, which is `array_length` in the following syntax.

CREATE TABLE <TABLE_NAME> (
    <VECTOR_COLUMN_NAME> float4[] CHECK (array_ndims(<VECTOR_COLUMN_NAME>) = 1 AND array_length(<VECTOR_COLUMN_NAME>, 1) = <DIM>)
)
WITH (
    vectors = '{
    "<VECTOR_COLUMN_NAME>": {
        "algorithm": "<ALGORITHM>",
        "distance_method": "<DISTANCE_METHOD>",
        "builder_params": {
            "<BUILDER_PARAMETERS_NAME>": <VALUE>
            [, ...]
        }
    }
    [ , "<VECTOR_COLUMN_NAME_2>": { ... } ]
  }'
);

Parameter description:

The value of the vector index parameter `vectors` must meet the following requirements:

• Only JSON-formatted strings are supported. At the top level, only one `vector_column_name` key is supported. This key specifies the name of the vector field for which to build a vector index.

• The value of the `vector_column_name` key is a JSON object used to configure vector index parameters. The following keys are supported.

The `builder_params` parameter supports the following parameters:

Modify an index

Syntax:

ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{
    "<VECTOR_COLUMN_NAME>": {
        "algorithm": "<ALGORITHM>",
        "distance_method": "<DISTANCE_METHOD>",
        "builder_params": {
            "<BUILDER_PARAMETERS_NAME>": <VALUE>
            [, ...]
        }
    }
  }'
);

Delete an index

-- Delete the vector indexes of all columns in a table.
ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{}'
);

-- If a table has vector indexes built on both the col1 and col2 columns, and you need to delete the index on the col2 column, run the ALTER TABLE statement to retain only the index on the col1 column.
ALTER TABLE <TABLE_NAME>
SET (
    vectors = '{
    "col1": { ... }
  }'
);

View an index

Hologres provides the `hologres.hg_table_properties` system table. You can use this table to view created vector indexes.

SELECT
    *
FROM
    hologres.hg_table_properties
WHERE 
    table_name = '<TABLE_NAME>'
    AND property_key = 'vectors';

Vector distance calculation functions

Vector retrieval in Hologres supports approximate retrieval and exact retrieval. Only approximate retrieval functions can use a created vector index to accelerate queries. The function must correspond to the `distance_method` of the vector index. Exact retrieval functions cannot use vector indexes.

Note: Vector distance calculation functions do not support all-constant input parameters.

Verify the use of a vector index

You can view the execution plan to check whether the SQL statement uses a vector index. If the plan contains "Vector Filter", the index is used successfully. For more information, see EXPLAIN and EXPLAIN ANALYZE.

• Example SQL:

  SELECT
      id,
      approx_euclidean_distance (feature, '{0.1,0.2,0.3,0.4}') AS distance
  FROM
      feature_tb
  ORDER BY
      distance
  LIMIT 40;

• Execution plan:

  Limit  (cost=0.00..182.75 rows=40 width=12)
    ->  Sort  (cost=0.00..182.75 rows=160 width=12)
          Sort Key: (VectorDistanceRef)
          ->  Gather  (cost=0.00..181.95 rows=160 width=12)
                ->  Limit  (cost=0.00..181.94 rows=160 width=12)
                      ->  Sort  (cost=0.00..181.94 rows=40000 width=12)
                            Sort Key: (VectorDistanceRef)
                            ->  Local Gather  (cost=0.00..91.53 rows=40000 width=12)
                                  ->  Limit  (cost=0.00..91.53 rows=40000 width=12)
                                        ->  Sort  (cost=0.00..91.53 rows=40000 width=12)
                                              Sort Key: (VectorDistanceRef)
                                              ->  Project  (cost=0.00..1.12 rows=40000 width=12)
                                                    ->  Index Scan using Clustering_index on feature_tb  (cost=0.00..1.00 rows=40000 width=8)
                                                          Vector Filter: VectorCond => KNN: '40'::bigint distance_method: approx_euclidean_distance search_params: {NULL} args: {feature'{0.100000001,0.200000003,0.300000012,0.400000006}'::real[]}
  Query Queue: init_warehouse.default_queue
  Optimizer: HQO version 4.0.0

Use vector indexes efficiently

When the data volume is small (for example, tens of thousands of rows) or the instance has sufficient computing resources, do not set a vector index. Use brute-force calculation instead. Use a vector index only when brute-force calculation cannot meet your requirements for latency, throughput, or other metrics. This is because:

• Vector indexes are lossy, so the accuracy (recall rate) cannot reach 100%.

• A vector index may return fewer items than requested. For example, a query with `LIMIT 1000` might return only 500 items.

When you choose to use a vector index, configure it as follows (using a non-partitioned table with a single 768-dimension vector field as an example):

• Latency-sensitive scenarios: Use an in-memory-only index. For the index quantization method, we recommend that you use `sq8_uniform` or `rabitq`. The recommended data volume is no more than 5 million rows per shard.

• Latency-insensitive or large data volume scenarios: Use a hybrid memory-disk index. For the index quantization method, we recommend that you use `rabitq`. The recommended data volume is no more than 30 million to 50 million rows per shard.

• Note: To set vector indexes on multiple columns, the recommended data volume per shard must be reduced proportionally. The vector dimension also affects this recommendation.

Examples:

-- Complete example of a hybrid index
CREATE TABLE feature_tb (
    id bigint,
  	feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    table_group = 'test_tg_shard_4',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "rabitq",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "precise_io_type": "reader_io",
            "use_reorder": true,
            "max_total_size_to_merge_mb" : 4096
        }
    }
    }'
);


-- Complete example of an in-memory-only index
CREATE TABLE feature_tb (
    id bigint,
  	feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    table_group = 'test_tg_shard_4',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb" : 4096
        }
    }
    }'
);

Improve the recall rate

This section uses the VectorDBBench dataset as an example to describe how to improve the recall rate.

Multiple factors can affect the recall rate. With the following index parameter settings, the default recall rate of the system can typically reach over 95%:

• Index parameters:

  • `base_quantization_type` is `rabitq` or `sq8_uniform`

  • `precise_quantization_type` is `fp32`

  • `max_degree` is `64`

  • `ef_construction` is `400`

• Query parameters (GUC):

  • `hg_vector_ef_search`: The recommended value is the default value of 80. This parameter controls the size of the candidate list during retrieval to balance accuracy and speed. A larger value provides higher accuracy but also increases resource overhead.

To further improve the recall rate to over 99%, you can run SET hg_vector_ef_search = 400; while keeping other parameters unchanged. However, improving the recall rate increases query latency and computing resource usage.

To further improve the recall rate to 99.5% to 99.7%, you can adjust the `max_degree`, `ef_construction`, and `hg_vector_ef_search` parameters. This increases query latency, query resource consumption, index building time, and index building resource consumption. For example:

• `max_degree` = 96.

• `ef_construction` = 500 or 600.

• `hg_vector_ef_search` = 500 or 600.

Set an appropriate shard count

A higher shard count results in more index files being built, which reduces the throughput of approximate vector queries. Therefore, set an appropriate shard count for your application. You can typically follow these steps:

1. Select appropriate instance specifications based on the volume of vector data. The following examples are for 768-dimension vectors. For information about other dimensions, see Recommended instance types for vector computing.

   1. In-memory-only index: 5 million vectors per worker.

   2. Hybrid memory-disk index: 100 million vectors per worker.

2. Determine the shard count based on the instance specifications. Typically, you can set the shard count to be equal to the number of workers. For example, for a 64 CU instance, you can set `shard_count` to 4.

   The following is an SQL example:

   -- Create a vector table and place it in a table group with a shard count of 4.
   CALL HG_CREATE_TABLE_GROUP ('test_tg_shard_4', 4);
   
   CREATE TABLE feature_tb (
       id bigint,
     	feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
   )
   WITH (
       table_group = 'test_tg_shard_4',
       vectors = '{
       "feature": {
           "algorithm": "HGraph",
           "distance_method": "Cosine",
           "builder_params": {
               "base_quantization_type": "sq8_uniform",
               "graph_storage_type": "compressed",
               "max_degree": 64,
               "ef_construction": 400,
               "precise_quantization_type": "fp32",
               "use_reorder": true,
               "max_total_size_to_merge_mb" : 4096
           }
       }
       }'
   );

Vector and scalar hybrid query scenarios

For vector retrieval with filter conditions, the following are common filtering scenarios:

Query scenario 1: A string column is used as a filter condition

The following is an example query. A common scenario is to find corresponding vector data within an organization, such as finding face data in a class.

SELECT(feature, '{1,2,3,4}') AS d FROM feature_tb WHERE uuid = 'x' ORDER BY d LIMIT 10;

We recommend the following optimizations:

• Set `uuid` as the distribution key. This ensures that data with the same filter condition is stored in the same shard. A query then only accesses a single shard.

• Set `uuid` as the clustering key for the table. Data is sorted within files based on the clustering key.

Query scenario 2: A time field is used as a filter condition

The following is an example query. This is typically used to filter vector data based on a time field. We recommend that you set the time field `time_field` as the segment key for the table. This lets you quickly locate the files where the data resides.

SELECT xx_distance(feature, '{1,2,3,4}') AS d FROM feature_tb WHERE time_field BETWEEN '2020-08-30 00:00:00' AND '2020-08-30 12:00:00' ORDER BY d LIMIT 10;

Therefore, for vector retrieval with any filter conditions, the `CREATE TABLE` statement is typically as follows:

-- Note: If you do not filter by time, you can remove the index related to time_field.
CREATE TABLE feature_tb (
    time_field timestamptz NOT NULL,
    uuid text NOT NULL,
    feature float4[] CHECK(array_ndims(feature) = 1 AND array_length(feature, 1) = 4)
)
WITH (
    distribution_key = 'uuid',
    segment_key = 'time_field',
    clustering_key = 'uuid',
    vectors = '{
    "feature": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "sq8_uniform",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb" : 4096
        }
    }
    }'
);

ASYNC REBUILD TABLE <table_name> 
WITH (
    rebuild_guc_hg_computing_resource = 'serverless'
)
SET (
    bitmap_columns = '<col1>,<col2>',
    dictionary_encoding_columns = '<col1>:on,<col2>:off',
    vectors = '{
    "<col_vector>": {
        "algorithm": "HGraph",
        "distance_method": "Cosine",
        "builder_params": {
            "base_quantization_type": "rabitq",
            "graph_storage_type": "compressed",
            "max_degree": 64,
            "ef_construction": 400,
            "precise_quantization_type": "fp32",
            "use_reorder": true,
            "max_total_size_to_merge_mb" : 4096
        }
    }
    }'
);

BEGIN ;
-- Clean up potential temporary tables.
DROP TABLE IF EXISTS <table_new>;
-- Create a temporary table.
SET hg_experimental_enable_create_table_like_properties=on;
CALL HG_CREATE_TABLE_LIKE ('<table_new>', 'select * from <table>');
COMMIT ;

-- Enable column-oriented storage for JSON-formatted data for the corresponding column.
ALTER TABLE <table_new> ALTER COLUMN <column_name> SET (enable_columnar_type = ON);
-- Create a full-text index on the corresponding column.
CREATE INDEX <idx_name> ON <table_new> USING FULLTEXT (column_name);

-- Insert data into the temporary table, use Serverless resources to run the job, and synchronously complete index building.
SET hg_computing_resource = 'serverless';
INSERT INTO <table_new> SELECT * FROM <table>;
ANALYZE <table_new>;

BEGIN ;
-- Delete the old table.
DROP TABLE IF EXISTS  <table>;
-- Rename the temporary table.
ALTER TABLE <table_new> RENAME TO <table>;
COMMIT ;