Skip to main content

CREATE INDEX

The CREATE INDEX statement in MotherDuck has differences from DuckDB. While the syntax is supported, indexes are not currently utilized for query acceleration in MotherDuck. This is generally not a concern as MotherDuck is already highly optimized for analytical workloads and provides excellent query performance through optimized data storage and processing.

Key Differences​

  • Indexes can be created but do not provide performance benefits
  • Queries that would use an index scan in DuckDB will use a sequential scan in MotherDuck instead

Example​

-- Create a table and an index
CREATE TABLE users(id INTEGER, name VARCHAR);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
CREATE INDEX idx_user_id ON users(id);

-- This query will use a sequential scan in MotherDuck
-- even though an index scan would be used in DuckDB
SELECT * FROM users WHERE id = 1;

You can verify this behavior using the EXPLAIN statement:

EXPLAIN SELECT * FROM users WHERE id = 100;
-- Will show SEQ_SCAN in MotherDuck
-- Would show INDEX_SCAN in DuckDB
note

While queries that would benefit from index acceleration in DuckDB will use different execution plans in MotherDuck, MotherDuck's architecture is designed to provide fast analytical query performance even without indexes. The platform uses various optimizations and a cloud-native architecture to ensure efficient query execution.

Additionally, it's worth noting that indexes can significantly slow down INSERT operations, as the index needs to be updated with each new record. Since indexes don't provide query acceleration benefits in MotherDuck, creating them will only add this overhead without any corresponding advantages.

For reference, you can learn more about how indexes work in DuckDB in their Indexes documentation.