Exploring DuckDB indexes

A database index is a data structure that is used to improve the speed of data retrieval operations. Like many databases, DuckDB uses indexes to facilitate quick access to specific data within a database table by avoiding the need to perform an exhaustive search of every record. There are, however, some significant differences in how you should approach using indexing with DuckDB. Unlike many database systems, DuckDB takes care of many of the indexing operations automatically for you. This means you can spend less time on administrative tasks, such as index design, and focus more on the query logic needed to reach your desired outcomes.

DuckDB automatically creates a min-max index against every column that is a built-in general-purpose data type. This is also known as a block range index (BRIN) (https://en.wikipedia.org/wiki/Block_Range_Index). These indexes work by sampling a range of column data (a block) and storing the minimum and maximum values for...