Troubleshoot ScaNN index errors

List of errors

The following a list of errors that are generated when you try to create a ScaNN index. You can disable generation of these errors and continue to generate the index. For more information, see Enforce index creation and suppress errors.

ERROR: Cannot create ScaNN index with empty table

Error message

When you try to generate an index on a table with no data or try to truncate a table with a ScaNN index generated on it, the following error occurs:

ERROR: Cannot create ScaNN index with empty table. Once the table is populated with data, create the index. See documentation to bypass this validation.

Sample queries that cause the error

• Query Example A

  create table t1 (a INT, b VECTOR(512));
  CREATE TABLE
  create index on t1 using ScaNN(b cosine) with (num_leaves = 10, quantizer = 'sq8');
  

• Query Example B

  truncate t1;
  

Recommended fix

Make sure that your table is populated with embedding vectors before you generate a ScaNN index.

ERROR: Cannot create ScaNN index

Error message

The following errors occurs in one of the following cases:

• When you try to generate an index on a table with few rows populated
• When you run VACUUM FULL on a table with a ScaNN index previously generated on it, that sometimes leave a small number of rows undeleted

Cannot create ScaNN index, error: INVALID_ARGUMENT: Number of row (5) must be larger than (1000).

Sample query that causes the error

• Query Example A

create table t1 (a INT, b VECTOR(512));
CREATE TABLE
insert into t1 select (random()*1e9)::int, random_vector(512) from generate_series(1, 5);
INSERT 0 5
create index on t1 using scann(b cosine) with (num_leaves = 100, quantizer = 'sq8');

• Query Example B

-- ... created a table t1
-- ... inserted some data to t1
-- ... successfully created an scann index on t1
DELETE FROM t1; -- delete all data under table t1
VACUUM FULL t1; -- BOOM!
-- VACUUM FULL will fail because it tries to create a new ScaNN index from the
-- empty table. VACUUM is not affected.

Recommended fix

Ensure that your table is populated with embedding vectors before you generate a ScaNN index. We recommend that the number of rows in the table are greater than the value defined in the num_leaves parameter.

In the case of VACUUM FULL, we recommend to use the regular VACUUM operation until the required number of rows is reached as suggested earlier.

ERROR: Cannot create ScaNN index on parent partition table.

Error message

If you have created partitioned tables from a parent table, then creating a ScaNN index on the parent table generates the following error:

ERROR: Cannot create ScaNN index on parent partition table. Create ScaNN indexes on the child tables instead. See documentation to bypass this validation.

Sample query that causes the error

create table t1 (a INT, b VECTOR(512)) partition by range(a);
CREATE TABLE
CREATE TABLE t1_one_ten PARTITION of t1 for values from (1) to (10);
CREATE TABLE
insert into t1_one_ten select (random()*1e9)::int, random_vector(512) from generate_series(1, 100);
INSERT 0 100
CREATE TABLE t1_eleven_twenty PARTITION of t1 for values from (11) to (20);
CREATE TABLE
insert into t1_eleven_twenty select (random()*1e9)::int, random_vector(512) from generate_series(1, 100);
INSERT 0 100
create index on t1 using scann(b cosine) with (num_leaves = 10, quantizer = 'sq8');

Recommended fix

You can't generate a ScaNN index on the parent table of a partitioned table. You must generate the ScaNN indexes on the partitioned table.

Enforce index creation and suppress errors

You can enforce AlloyDB to generate an index and suppress errors. Before allowing index generation with this method, consider the following implications:

• Since the index is trained on less or no data, centroids learn on zero data leading to bad recall.
• The write performance to the database might also be slow.

To force index generation, complete the following:

1. Set the scann.allow_blocked_operations creation session-level parameter to true on the database:

   SET scann.allow_blocked_operations = true;
   

2. Assign the SUPERUSER privilege to the user that will run these queries on the database:

   CREATE USER USER_NAME WITH SUPERUSER PASSWORD PASSWORD;
   

   Replace the following:

   • USER_NAME: the name of the user you want to grant the privilege to.
   • PASSWORD: the password of the user.