Leis et al. explored the effectiveness of query optimizers in 2015 and revisited the topic a decade later. Their findings indicate that query optimizers still have significant limitations. The complexity of query optimization, particularly in join ordering, is highlighted as an NP-hard problem. However, verifying the quality of a query plan is more straightforward, as a good optimizer produces faster-running plans. Language models can effectively learn to optimize for execution time, leading to faster query plans.
An experiment was conducted to determine if a small, open-weights model could be fine-tuned through supervised learning and reinforcement learning to generate Postgres query plans that outperform the default plans. The results confirmed that this approach is viable. For instance, using the IMDb dataset, a query was constructed to identify Japanese companies with the most titles in the 2000s. The execution path taken by Postgres to retrieve this data is influenced by selective predicates, which are the filtering conditions in a WHERE clause.
The analysis included various join trees and cardinalities, illustrating how different join orders can significantly affect performance. The study noted that Postgres cannot accurately count cardinalities during query planning, relying instead on statistical estimates from the pg_statistic table. This reliance on uniform distribution assumptions can lead to poor performance if the assumptions do not hold true.
To address this, the third-party extension pg_hint_plan allows users to provide hints to the Postgres optimizer, potentially steering it towards more efficient query plans. The research raises the question of whether a language model can learn to generate hints that lead to improved query plans.