Introduction
Hello, this is a summary of the book PostgreSQL Query Optimization Second Edition
after reading mysql
now that I am professionally working with postgresql I want to learn more about optimizing queries in postgresql.
These types of summaries are not really for audiences but for me to remember what I read and to be able to reference them later.
AI tools these days are so good at this but I like to do this type of summaries for my own learning.
Actually each chapter in the book have a summary so :)
Chapter 1: Why Optimize?
In this chapter the authors claims that we should optimize the queries the first time we write them, and that we should not wait until the database is slow to optimize the queries.
How much performance is good performance? the authors claim that the answer is that it depends on the application and the users.
they recommend a framework to optimize queries which is: SMART
- Specific
- Measurable
- Achievable
- Relevant
- Time-bound
it’s good to keep in mind that performance from user perspective is the end-to-end time from the user request to the response. Not just the time it takes to execute the query.
It’s good to keep an eye on performance even if we optimized a query today, because the data changes and the query might not be optimal anymore.
So we should keep monitoring the performance of the queries with alerts and automated monitoring tools.
All optimizations related to other databases don’t apply to postgresql, because postgresql do somethings differently than other databases.
For example postgresql doesn’t have hints to the optimizer, postgresql developers didn’t do that because their intention was to write a good optimizer that doesn’t need hints.
And they did a good job at that, the postgresql optimizer is one of the best optimizers out there.
Chapter 2: Theory: Yes, We Need It!
This chapter cover some basics from relational theory ( same covered in my university courses, so will not cover it here )
In order to produce query results, the database system must first parse the query, compile and transfer, then optimize it, and finally execute it.
The database engine parse SQL queries into a logical plan transform the results then chose algorithms to implement the logical plan and finally execute the plan.
These operations are derived from relational theory.
