Slow SQLite Query: GROUP BY MIN/MAX Not Using Primary Key

Understanding the Query Performance Issue with GROUP BY MIN/MAX

The core issue revolves around a seemingly simple query that performs poorly despite the presence of a well-structured primary key. The table in question, origin, is defined with a composite primary key on (ticker, date). The query aims to retrieve the minimum and maximum date for each ticker using a GROUP BY clause. However, the query execution plan reveals a full table scan (SCAN TABLE origin), which is unexpectedly slow. This behavior persists even after adding an index identical to the primary key and running ANALYZE to update statistics.

The primary key, which should theoretically optimize such queries, is not being utilized effectively. This raises questions about SQLite’s query optimization capabilities, particularly for specialized queries involving both MIN and MAX aggregations within a GROUP BY. The discussion also explores alternative solutions, such as using correlated subqueries and Common Table Expressions (CTEs), which significantly improve performance. However, the underlying question remains: why doesn’t SQLite leverage the primary key for this query?

Why SQLite Fails to Use the Primary Key for MIN/MAX Aggregations

SQLite’s query optimizer is designed to handle a wide range of queries efficiently, but it has limitations when dealing with highly specialized queries. In this case, the query requests both the minimum and maximum date for each ticker within a single GROUP BY operation. SQLite’s optimizer does not include a specific optimization for this scenario, leading it to default to a full table scan. This is because the optimizer may determine that it needs to read most or all rows to compute both the minimum and maximum values, making a full scan seem like the most straightforward approach.

Additionally, the structure of the origin table plays a role in the optimizer’s decision-making process. The table is defined as WITHOUT ROWID, meaning it uses the primary key as the physical storage key. While this design can improve performance for certain queries, it does not guarantee optimal performance for all query types. In this case, the optimizer may not recognize that the primary key can be used to efficiently compute the required aggregations.

Another factor is the distribution of data within the table. If the rows are not stored in an order that aligns with the query’s requirements, the optimizer may struggle to find an efficient execution plan. For example, if the rows are inserted in date order rather than ticker order, the table may become fragmented from the perspective of ticker-based queries. This fragmentation can further degrade performance, as the database engine may need to perform additional I/O operations to retrieve the necessary data.

Optimizing the Query: Correlated Subqueries and CTEs

To address the performance issues, the discussion suggests using correlated subqueries and CTEs as an alternative to the GROUP BY approach. The proposed solution involves first extracting the distinct ticker values using a CTE and then using correlated subqueries to compute the minimum and maximum date for each ticker. This approach leverages SQLite’s ability to perform skip scans on the primary key, which can significantly reduce the number of rows that need to be scanned.

The optimized query looks like this:

WITH
  tickers (ticker) AS
    (SELECT DISTINCT ticker FROM origin)
SELECT
  ticker,
    (SELECT MIN(date) FROM origin
       WHERE origin.ticker = tickers.ticker) AS "min(date)",
    (SELECT MAX(date) FROM origin
       WHERE origin.ticker = tickers.ticker) AS "max(date)"
  FROM tickers;

This query is approximately eight times faster than the original GROUP BY query. The key to its performance lies in the way it breaks down the problem into smaller, more manageable parts. By first isolating the distinct ticker values, the query reduces the scope of the subsequent aggregations. The correlated subqueries then efficiently compute the minimum and maximum date for each ticker by leveraging the primary key.

It is important to note that this approach relies on the statistics gathered by ANALYZE. Without up-to-date statistics, the optimizer may not be able to make informed decisions about the most efficient execution plan. Running ANALYZE ensures that the optimizer has the necessary information to choose the best strategy for each part of the query.

Additional Considerations: Fragmentation and Vacuuming

The discussion also touches on the potential impact of table fragmentation on query performance. Fragmentation occurs when rows are not stored in a contiguous manner, leading to increased I/O operations as the database engine retrieves data from scattered locations. In the context of the origin table, fragmentation could occur if rows are inserted in date order rather than ticker order. This would result in rows for the same ticker being spread across different parts of the table, making it more difficult for the optimizer to retrieve them efficiently.

To address fragmentation, the discussion suggests running the VACUUM command. VACUUM reorganizes the table’s storage, ensuring that rows are stored in a more contiguous manner. However, in this specific case, the user reports that VACUUM does not improve performance. This suggests that fragmentation is not the primary cause of the slowdown, at least in this instance.

Instead, the focus should be on ensuring that the optimizer has accurate statistics to work with. Running ANALYZE updates the database’s internal statistics, enabling the optimizer to make better decisions about query execution plans. In this case, ANALYZE plays a crucial role in the performance of the optimized query, as it allows the optimizer to recognize the benefits of using the primary key for skip scans.

Conclusion: Best Practices for Optimizing SQLite Queries

The discussion highlights several important lessons for optimizing SQLite queries, particularly when dealing with complex aggregations and large datasets. First, it is essential to understand the limitations of SQLite’s query optimizer and recognize when a query may fall outside its optimization capabilities. In such cases, breaking the query into smaller, more manageable parts—such as using CTEs and correlated subqueries—can lead to significant performance improvements.

Second, the structure and storage of the table can have a significant impact on query performance. While WITHOUT ROWID tables can improve performance for certain queries, they do not guarantee optimal performance for all scenarios. Understanding how data is stored and accessed can help identify potential bottlenecks and guide optimization efforts.

Finally, maintaining up-to-date statistics is crucial for enabling the optimizer to make informed decisions. Running ANALYZE regularly ensures that the optimizer has the information it needs to choose the most efficient execution plan. By combining these best practices with a thorough understanding of SQLite’s capabilities and limitations, developers can achieve optimal performance for even the most challenging queries.