What happens?

When copying a database into another using the COPY FROM DATABASE ... TO syntax any indexes are copied but it looks like they are not associated with the table correctly and they are not used in any queries.

The reason we would like to be able to do this is to create a database on disk with indexes and then later load the whole database into memory on another system to speed up queries, without having to re-create all indexes.

To Reproduce

An example to show the behavior, creating some data in a database db1, creating an index and then copying to a new database db2:

ATTACH ':memory:' AS db1;
USE db1;
CREATE TABLE Data AS SELECT i, hash(i)::VARCHAR AS value FROM generate_series(1, 1000000) s(i);
ALTER TABLE Data ALTER COLUMN value SET NOT NULL;
CREATE INDEX data_value ON Data(value);
ATTACH ':memory:' AS db2;
COPY FROM DATABASE db1 TO db2;

The index is copied into db2 and listed in duckdb_indexes but the table in db2 shows index_count = 0:

SELECT * FROM duckdb_indexes;
SELECT database_name, table_name, index_count FROM duckdb_tables;

The index is not used in queries.

OS:

Debian Linux in docker using python:3.12-bookworm image

DuckDB Version:

1.1.3

DuckDB Client:

duckdb cli

Hardware:

No response

Full Name:

Olof Sjöbergh

Affiliation:

Booli Search Technologies

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have