Learning what the PostgreSQL optimizer does
Before even attempting to think about query performance, it makes sense to familiarize yourself with what the PostgreSQL query optimizer does. Having a deeper understanding of what’s going on under the hood makes a lot of sense because it helps you see what the database is really up to. Keep in mind that the optimizer is a part of PostgreSQL. It’s constantly evolving and more optimizations are being added as we speak. It’s impossible to cover every corner of the software but it’s vital to have at least some basic understanding of what’s happening under the hood deep inside the “engine room” of PostgreSQL.
A practical example – how the query optimizer handles a sample query
To demonstrate how the optimizer works, I’ve compiled an example. It’s something that I’ve used over the years for PostgreSQL training. Let’s assume that there are three tables, as...
