Guides
ART Indexes
DuckDB allows defining [Adaptive Radix Tree (ART) indexes](https://db.in.tum.de/~leis/papers/ART.pdf) in two ways.
First, such an index is created implicitly for columns with `PRIMARY KEY`, `FOREIGN KEY`, and `UNIQUE` [constraints](#docs:guides:performance:schema::constraints).
Second, explicitly running a the [`CREATE INDEX`](#docs:sql:indexes) statement creates an ART index on the target column(s).  
The tradeoffs of having an ART index on a column are as follows:  
1. It enables efficient constraint checking upon changes (inserts, updates, and deletes) for non-bulky changes.
2. Having an ART index makes changes to the affected column(s) slower compared to non-indexed performance. That is because of index maintenance for these operations.  
Regarding query performance, an ART index has the following effects:  
1. It speeds up point queries and other highly selective queries using the indexed column(s), where the filtering condition returns approx. 0.1% of all rows or fewer. When in doubt, use [`EXPLAIN`](#docs:guides:meta:explain) to verify that your query plan uses the index scan.
2. An ART index has no effect on the performance of join, aggregation, and sorting queries.  
Indexes are serialized to disk and deserialized lazily, i.e., when the database is reopened, operations using the index will only load the required parts of the index. Therefore, having an index will not cause any slowdowns when opening an existing database.  
> **Best practice.  ** We recommend following these guidelines:
>
> * Only use primary keys, foreign keys, or unique constraints, if these are necessary for enforcing constraints on your data.
> * Do not define explicit indexes unless you have highly selective queries.
> * If you define an ART index, do so after bulk loading the data to the table. Adding an index prior to loading, either explicitly or via primary/foreign keys, is [detrimental to load performance](#docs:guides:performance:schema::microbenchmark-the-effect-of-primary-keys).