A surprising finding: partial indexes can actually be slower than full table scans
Introduction
PostgreSQL, renowned for its reliability and performance, often uses comprehensive indexing strategies to optimize query execution times. However, as demonstrated in the article "Don't pick a partial index for speed," sometimes choosing an overly selective index strategy might not yield the expected benefits. This blog post explores the findings of this study with a deeper dive into their specific context.
What Was Studied
The research focused on a scenario involving a queue table that contains approximately 10 million rows, and they experimented with three different data sizes: 2M, 5M, and 10M. They also examined how various indexing strategies affected the performance of their query workload. The four index types considered were:
A full table scan.
An index only on a single column.
A partial index that excluded up to half of the rows (50% exclusion).
Another partial index strategy that was more selective, excluding up to 90% of the data.
The experiment used pgbench, PostgreSQL's benchmark utility, to simulate the workload and measure query execution times.
Experiment Results
Full Table Scan vs. Partial Index Excluding Up To 50%
When the data size was 2M rows or smaller, both partial indexes outperformed the full table scan by a significant margin. However, with larger datasets (5M and 10M), the partial index that excluded up to half of the rows performed poorly compared to the other options.
Partial Index Excluding Up To 90%
Interestingly, when the data size increased further to 5M rows, excluding only up to 90% of the data, both partial indexes became even more suboptimal than a full table scan. This finding contradicts the initial conclusion derived from this experiment, where it appeared that such selective indexing strategies would be advantageous.
Conclusion
The article's original recommendation suggested that partial indexes are beneficial for performance optimization and could drastically reduce query execution times by avoiding unnecessary scans over large datasets. However, the findings revealed a critical flaw in this interpretation: while full table scans performed poorly with smaller data sets (2M rows), they became significantly faster at larger scales (5M and 10M). This suggests that partial indexes might not always be an optimal choice for speeding up queries.
More critically, one specific setting was found to reverse the performance benefits of both partial indexing strategies. Adjusting a certain parameter led to these previously suboptimal options turning into significantly slower alternatives than even the full table scan method. The takeaway from this study is that choosing index strategies should not only consider initial dataset sizes but also the overall context and parameters involved in query execution.
Conclusion
In summary, while partial indexing offers attractive performance benefits for smaller datasets, its effectiveness diminishes dramatically as data volume increases, making it less suitable for optimization purposes. In such scenarios, a full table scan might prove to be the more efficient choice despite initial intuition suggesting otherwise. Understanding these nuances can help in designing more robust and scalable database systems tailored to specific workload demands.