Understanding query tuning

About query tuning

Query tuning refers to allowing the database optimizer to create a better execution plan for queries to optimize database performance. I've seen back-end developers talk about why they need to know about query tuning, but personally, I think it's something that you need to know when dealing with servers and databases. And if you know query tuning, you can naturally learn about the query itself and understand native query and ORM more easily.

1. Query Processing

In PostgreSQL, there is a parser stage, where the parser turns a query into a parse tree, and through the transformation process, the meaning of the parse tree is analyzed to create a query tree. (It was written based on the document, and there are also materials where this part is marked as analyzer.) The rewriter then rewrites the query tree according to a set system of rules. The planner creates a plan tree to be executed based on the query tree. This part corresponds to what we commonly refer to as the optimizer. Finally, queries are executed in the order of plan tree creation using the excutor.

Parser

First, the parser performs parsing based on the query. This is the step to check if the sql grammar is correct. I am not interested in the content of the query itself.

Transformation

After the tree is created like this, a query tree is created through the transformation process. Previously, we only did parsing, but this time we go through the process of checking whether the tables and columns in the actual query are actually valid contents. During the transaction process, the two parts are divided into a parser and a parser to check the system catalog, where the system catalog is a place that contains the metadata of the database.

Rewriter

The rewriter stage is the stage where a rule system is implemented based on the query tree.

Planner

The planner stage finds data using the query tree received from the rewriter stage and creates a plan tree for an execution plan. We use an efficient method by calculating costs according to the execution plan. According to the official document, because multiple joins consume excessive time and memory, Genetic Query Optimizer is used when a certain number of joins are exceeded.

Exector

In the exector stage, data is processed by sequentially executing the nodes of the plan tree. If you look at the query results processed through explain analysis, you can obtain the following data. WindowAgg (Window Aggregate) indicates cost, number of rows returned, time, etc. Planning time is the time it takes PostgreSQL to develop an execution plan. Execution time refers to the time it takes to execute a query.

2. Query Execution Plan

The query plan can be obtained using ``EXPLAIN``` as follows:

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT
zip,
ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank;
RANK() OVER (PARTITION BY type ORDER BY price DESC) AS type_price_rank
FROM
test
WHERE
city = 'SACRAMENTO';

• analyze Analyzes the actual execution plan and provides information such as execution speed
• costs Cost of executing a query
• verbose more detailed information about execution plan, what indexes, what table scans
• buffers Buffer information, buffer for each stage
• format json To return the execution plan as json

This can be obtained through visualized data. You can see the execution plan in the picture in order, and the table below shows the execution plan.

• exclusive Time spent on a specific step
• inclusive Total time including that step + sub-plan steps
• rowx is the number of rows expected at each step
• actual is the number of rows returned in the actual step
• plan is information related to the execution planning method of the execution planning stage Let's look again at the query plan shown earlier. WindowAgg (Window Aggregate) indicates cost, number of rows returned, time, etc. The first parentheses are based on the expected plan, cost indicating the starting and ending costs of the execution plan expected by the optimizer, rows the expected number of rows, width the width of the result set, meaning the width of each row is 116 bytes. The next parentheses indicate the actual execution time, number of rows actually processed, and number of loops. planning time is the time it takes PostgreSQL to develop an execution plan. Execution time refers to the time taken to execute the query. When reading the query execution plan, read it from the inside out and, if indicated with the same indentation, from the top to the bottom.

3. Query Tuning Approaches

Before giving a presentation on query tuning, I searched for a variety of materials, but there were so many different approaches and stories for each database and level of difficulty that I thought it would be impossible to include everything. So, I set the topic of my presentation to introduce only a few of the most essential parts. As this is an event that many people of various ages participate in, we have included enough content to ensure that it is not boring. It is difficult to include all the presentation materials, so I will try to summarize only these parts by including four key points.

Where Let's narrow down the search range using the order of conditional clauses and Like.

In cases where there is a lot of data, there were cases where performance differences occurred due to the order of the where conditional clause. There are cases where you search by placing % wildcards on both sides of the like condition, but in this case, you cannot use the index and it takes time to look up the entire table. However, if you put a string in front, the beginning is fixed and you can use the index.

Let’s know the index type and set the index on the required column.

Before creating an index, you need to consider whether it is really the best option. This is because unnecessary indexes may actually reduce the performance of other queries. Nevertheless, if an index is needed, it is recommended to place an index on a column that is frequently queried and infrequently written, a column with few duplicate values, and a column that is likely to be used in arithmetic functions. When adding, you must use history to check the correlation with other queries when using the index. In PostgreSQL, if you create an index without specifying it, it is created as a b-tree index by default. It is also advantageous in ORDER BY queries or GROUP BY queries because it stores data in sorted order with an index suitable for queries using operators such as BETWEEN, >, <, >=, and <=. Others include Hash index, GiST (Generalized Search Tree) index, GIN (Generalized Inverted Index) index, and BRIN (Block Range Index) index.

The efficiency of each scanning method varies depending on the amount of resulting data.

Scan is a method used to retrieve data and is located at the lowest node in the execution plan. It can be said that it starts first. There are Bitmap Index Scan and TID Scan methods, but here we will explain the index scan method, which can be confusing. Index Scan: Use the index to find a key that meets the conditions and access the table row using that key. Suitable for range searches or sorted data. This can be relatively slow because it requires access to actual table data. Index-Only Scan: Used when an index contains all the data needed for a query. Get data from an index without having to access the actual table. Provides very fast performance. Bitmap Index Scan: It is suitable for queries that use multiple conditions or indexes. After displaying the rows that meet the conditions as a bitmap, search the rows in the table. Efficient for medium to large result sets.

If the result value is small, create an index column for Nested Join!

Nested Loop Join is the simplest and most common join method. It sequentially scans the outer table (outer relation), finding for each row a matching row in the inner table (inner relation). It is efficient when external tables are small and is often used in OLTP workloads. And nested loop joins can be significantly faster if there is an index on the join key of the inner table.