AnalyticDB:Create an HNSW vector index

Step 1: Create the table

CREATE TABLE chunks (
    id SERIAL PRIMARY KEY,
    chunk VARCHAR(1024),
    intime TIMESTAMP,
    url VARCHAR(1024),
    feature REAL[]
) DISTRIBUTED BY (id);

Step 2: Set the vector column storage to PLAIN

ALTER TABLE chunks ALTER COLUMN feature SET STORAGE PLAIN;

Setting the storage mode to PLAIN avoids column decompression on each row scan, which reduces overhead and improves query performance.

Step 3: Create the vector index

Create an index that matches the distance measure you plan to use in queries. The index type must match the query operator — a mismatch causes the query to fall back to an exact (brute-force) search.

L2 (Euclidean) distance — use <-> in queries:

CREATE INDEX idx_chunks_feature_l2
ON chunks USING ann(feature)
WITH (dim=1536, distancemeasure=l2, hnsw_m=64, pq_enable=1);

Inner product distance — use <#> in queries:

CREATE INDEX idx_chunks_feature_ip
ON chunks USING ann(feature)
WITH (dim=1536, distancemeasure=ip, hnsw_m=64, pq_enable=1);

Cosine distance — use <=> in queries:

CREATE INDEX idx_chunks_feature_cosine
ON chunks USING ann(feature)
WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

Create vector indexes only as needed. Multiple vector columns per table and multiple indexes per column are supported, but unused indexes add overhead.

Step 4: Index structured columns for hybrid queries

CREATE INDEX ON chunks(intime);

Indexing structured columns like intime improves performance when combining vector similarity with filters.

Asynchronous building with parallel processing

AnalyticDB for PostgreSQL supports concurrent index building via the fastann.build_parallel_processes Grand Unified Configuration (GUC) parameter. Set it to match the number of cores on your compute nodes.

Build with DMS:

On DMS, all SET statements and the CREATE INDEX statement must be on the same line to take effect together.

SET statement_timeout = 0;SET idle_in_transaction_session_timeout = 0;SET fastann.build_parallel_processes = 8;CREATE INDEX ON chunks USING ann(feature) WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

Build with psql:

1. Save the following to create_index.sql:

   SET statement_timeout = 0;
   SET idle_in_transaction_session_timeout = 0;
   SET fastann.build_parallel_processes = 8;
   CREATE INDEX ON chunks USING ann(feature)
   WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

2. Run the file:

   psql -h gp-xxxx-master.gpdb.rds.aliyuncs.com -p 5432 -d dbname -U username -f create_index.sql

Supported functions

Category	Function	Return type	Description	Supported data types
Distance	l2_distance	double precision	Euclidean distance (square root). Note In v6.3.10.17 and earlier, returns squared Euclidean distance. In v6.3.10.18 and later, returns the square root value.	smallint[], float2[], float4[], real[]
Distance	inner_product_distance	double precision	Inner product distance. Equals cosine similarity after normalization. Formula:	smallint[], float2[], float4[], real[]
Distance	cosine_similarity	double precision	Cosine similarity. Range: [-1, 1]. Measures directional similarity regardless of vector length. Formula:	smallint[], float2[], float4[], real[]
Distance	dp_distance	double precision	Dot product distance. Identical to inner product distance. Formula:	smallint[], float2[], float4[], real[]
Distance	hm_distance	integer	Hamming distance (bitwise).	int[]
Sorting	l2_squared_distance	double precision	Squared Euclidean distance. Skips the square root calculation for faster distance-based sorting. Formula:	smallint[], float2[], float4[], real[]
Sorting	negative_inner_product_distance	double precision	Negated inner product distance. Used for descending sort by inner product. Formula:	smallint[], float2[], float4[], real[]
Sorting	cosine_distance	double precision	Cosine distance. Range: [0, 2]. Used for descending sort by cosine similarity. Formula:	smallint[], float2[], float4[], real[]
Arithmetic	vector_add	smallint[], float2[], or float4[]	Sum of two vector arrays.	smallint[], float2[], float4[], real[]
Arithmetic	vector_sub	smallint[], float2[], or float4[]	Difference between two vector arrays.	smallint[], float2[], float4[], real[]
Arithmetic	vector_mul	smallint[], float2[], or float4[]	Element-wise product of two vector arrays.	smallint[], float2[], float4[], real[]
Arithmetic	vector_div	smallint[], float2[], or float4[]	Element-wise quotient of two vector arrays.	smallint[], float2[], float4[], real[]
Aggregation	vector_sum	int or double precision	Sum of all elements in a vector.	smallint[], int[], float2[], float4[], real[], float8[]
Aggregation	vector_min	int or double precision	Minimum element value.	smallint[], int[], float2[], float4[], real[], float8[]
Aggregation	vector_max	int or double precision	Maximum element value.	smallint[], int[], float2[], float4[], real[], float8[]
Aggregation	vector_avg	int or double precision	Average element value.	smallint[], int[], float2[], float4[], real[], float8[]
Aggregation	vector_norm	double precision	Norm (length) of a vector.	smallint[], int[], float2[], float4[], real[], float8[]
Aggregation	vector_angle	double precision	Angle between two vectors.	smallint[], float2[], float4[], real[]

Examples:

-- Euclidean distance
SELECT l2_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Inner product distance
SELECT inner_product_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Cosine similarity
SELECT cosine_similarity(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Dot product distance
SELECT dp_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Hamming distance
SELECT hm_distance(array[1,0,1,0]::int[], array[0,1,0,1]::int[]);
-- Vector addition
SELECT vector_add(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Vector subtraction
SELECT vector_sub(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Vector multiplication
SELECT vector_mul(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Vector division
SELECT vector_div(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Element sum
SELECT vector_sum(array[1,1,1,1]::real[]);
-- Minimum element
SELECT vector_min(array[1,1,1,1]::real[]);
-- Maximum element
SELECT vector_max(array[1,1,1,1]::real[]);
-- Average element
SELECT vector_avg(array[1,1,1,1]::real[]);
-- Vector norm
SELECT vector_norm(array[1,1,1,1]::real[]);
-- Angle between vectors
SELECT vector_angle(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);

Supported operators

All distance operators use the ANN access method and trigger index-accelerated search when a matching vector index exists. If no matching index exists, the query falls back to an exact search.

Examples:

-- Euclidean distance
SELECT array[1,1,1,1]::real[] <-> array[2,2,2,2]::real[] AS score;
-- Inner product distance
SELECT array[1,1,1,1]::real[] <#> array[2,2,2,2]::real[] AS score;
-- Cosine distance
SELECT array[1,1,1,1]::real[] <=> array[2,2,2,2]::real[] AS score;

-- Addition
SELECT array[1,1,1,1]::real[] + array[2,2,2,2]::real[] AS value;
-- Subtraction
SELECT array[1,1,1,1]::real[] - array[2,2,2,2]::real[] AS value;
-- Multiplication
SELECT array[1,1,1,1]::real[] * array[2,2,2,2]::real[] AS value;
-- Division
SELECT array[1,1,1,1]::real[] / array[2,2,2,2]::real[] AS value;