I didn’t post anything about optimization for a while, so I decided to write a short follow-up on one discussion I recently had.
I want to highlight today that when it comes to Postgres, it has the best optimizer ever. Most of the time, the only and the best thing a SQL developer can do are to allow the query planner to do its job.
Anybody who writes SQL knows that any optimizer tries to choose the best execution plan for a query. Still, the common assumption is that “the best execution plan” means choosing the right index to use, or not to use indexes altogether.
This assumption leaves out of consideration two essential steps. First, the query rewrite happens before any cost-based optimization is performed. One of the things that happen on that stage is replacing views with their SQL code. One of the biggest misconceptions which SQL textbooks introduce is that “you can use a view the same way as a table.” This by itself can potentially block the optimizer capabilities, since the total number of joins may easily exceed join_collapse_limit. Views with “group by” present even more problems, but enough about views. As I said multiple times, I hate views :).
Another subject of rewriting is eliminating subqueries. This is another example of what people often forget. Consciously or subconsciously, but people often believe that when they write something like
SELECT * FROM loan WHERE loan_id IN (….)
whatever is IN(…) will be executed first. It won’t. This subquery will be rewritten into a JOIN, and then optimized according to the cost-based rules.
And finally the last misconception of a similar kind. No matter how many times we say that the order of joins is not determined by the order the tables appear in the SELECT statement, there is an internal belief that by default, Postgres follows that order. It does not. The only time it does it when the number of tables exceeds the join_collapse_limit parameter. Moreover, for the same SELECT with different selection criteria, the order of joins may be different.
The conclusion is my usual one: always try to clearly specify what you need, and check the execution plan. You may be surprised 🙂
The World of Data 