PostgreSQL optimizer is good enough

The optimizer is the heart and soul of a relational DBMS. It analyzes SQL statements and determines the most efficient access plan for satisfying each statement. For database optimizers, there have always been many voices among database-related practitioners.

Optimizers for other databases

Many Oracle DBAs are intoxicated by the powerful SQL optimization capabilities of Oracle Database. For the same SQL statement, even if it is poorly written, it can generate a more efficient execution plan for you. Once they got used to the intelligent behavior of Oracle Optimizer, it was hard to embrace other relational database products. For SQL optimization, Oracle also provides a number of interfaces for adjusting execution plans, such as SQL annotations with execution plan prompts, SQL Profile to adjust SQL execution plans online.

For DBAs or developers who are familiar with MySQL, they pay little attention to the optimizer, and may not even be aware of the optimizer’s presence in the database. For SQL optimization, they generally look to see if the correct index is selected; For SQL for table joins, their advice is to simply not use it when developing applications. In fact, early versions of MySQL did not have an optimizer, that is, the SQL statement was parsed and executed.

PostgreSQL optimizer

The PostgreSQL database has a complete optimizer, and in most cases, it is possible to determine a more efficient execution plan for complex SQL. But, like Oracle, there are blind spots for optimization. In summary, the behavior of the PostgreSQL optimizer is also simple and easy to understand, for an SQL statement, based on the statistics collected by the database, in a time acceptable to the user, to determine the most efficient execution plan it deems. When you find that the performance of an SQL statement is poor, analyze the execution plan of the SQL and rewrite the SQL statement so that the database can generate a more efficient execution plan.

PostgreSQL optimizer improvements

The PostgreSQL community is constantly improving the database optimizer. When you find an SQL statement with optimization blind spots, the best course of action is to report it to the community. The community has many optimizer experts who will give detailed and adequate opinions on the reported issues, and the discussion period can last a long time, during which it is necessary to repeatedly test and verify and analyze the discussion. This full discussion is necessary because minor adjustments to the optimizer may have some unintended effects on SQL statements in other cases.

Development of the PostgreSQL optimizer

So, does the PostgreSQL database need to go a step further like Oracle and provide some interface for tuning execution plans?

On the whole, this kind of interface for adjusting the execution plan is more like some magic trick for optimizing application performance. They are not much different from other techniques in application software development. Like what:

• In the process of software development, you can implement a piece of code logic in assembly language to make the application get better running performance;
• Use some specific instructions specific to a certain type of CPU architecture to implement specific code logic;
• Adopt some specific interfaces specific to a certain operating system to implement specific business functions;

There are many such techniques available, but they all present a typical problem with your application: your application is poorly versatile and platform compatible.

Think about the deployment of your application software, after which you also need to configure SQL Profile in the database. What’s more troubling is that these configurations may have been manually modified by DBAs temporarily, or even they themselves forgot what changes have made. For SQL annotations with execution plan prompts, some unexpected behavior changes may occur at any time due to database version upgrades. This result will have a very bad impact on the development of application software.

If you think about these questions, you may understand why the PostgreSQL database does not provide such interfaces for adjusting execution plans. Even if the PostgreSQL community has plugins like pg_hint_plan that indicate the choice of execution plan via SQL comments, such features are difficult to be accepted and merged into the kernel by the community.