When your SQL statement returns the correct result, does it end there? You guessed right, it does not. Performance is an important aspect of every program and while writing SQL queries, it is important to consider the efficiency of every block of query. In this series of articles, we will explore query optimization techniques in Postgresql.
What makes SQL different?
SQL is a declarative language, which means we only specify the result we want, not how to get it. This is different from imperative languages, where we specify the sequence of steps to execute. This difference can lead to different execution times for two queries that yield the same result.
In Postgresql, query optimization focuses on choosing the best way of writing queries. The best way in this context is determined by different factors, such as storage structures, indexes,and data statistics
Postgresql Optimization Procedure
Postgresql opimization follows the SMART goals namely:
- Specific
- Measurable
- Achievable (attainable)
- Result-based (relevant)
- Time-bound (time-driven)
The table below shows what each goals represents while working with Postgresql.
| Goals | Not SMART Examples | SMART Examples |
|---|---|---|
| Specific | Pages should load quickly. | Each function should complete within a system-defined timeout. |
| Measurable | Customers should not have to wait long to complete their application. | The registration page should load in no more than 4 seconds. |
| Achievable | Daily data refresh time in the data warehouse should never increase. | The daily data refresh time should grow logarithmically as the source data volume grows. |
| Result-based | Each report refresh should be as fast as possible. | Each report refresh should be short enough to avoid lock waits. |
| Time-bound | We will optimize as many reports as we can. | By the end of the month, all financial reports should run in under 30 seconds. |
Postgresql Query Processing
Postgresql takes some keys steps to precess queries after we hit execute command. Below are the steps and what they represent:
- Compilation: Here Postgresql compiles and transforms a SQL statement into an expression consisting of high-level logical operations, known as a logical plan.
- Optimization: Postgresql optimizes the logical plan and converts it into an execution plan.
- Execute: In this final step, Postgresql interpret the plan and return results.
In the next article, we will look at code examples of these optimization techniques we can use when executing Postgresql query
Top comments (0)