Shulou(Shulou.com)06/01 Report--

This article will explain in detail the example analysis of the PostgreSQL cost model. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

As the main database that can replace oracle at present, understanding the cost model of POSTGRESQL is helpful to understand the possible root causes and solutions of problems when analyzing SQL statements and optimizing SQL statements.

For ORACLE, the cost model of a database like SQL SERVER is generally not disclosed to the outside world, so we see that some COST is also confused and confused.

PostgreSQL is open on the cost model, which helps operators and developers understand where poor performance queries occur.

First of all, let's analyze where the possible cost points are. Here is the approximate order and name of a plan tree.

1 Seq Scan

2 sort

3 Materialize

4 index scan

5 Merge left join

6 group aggregate

7 hashaggregate

Design cost model for POSTGRESQL

1 Seq_page_cost = Cs

2 random_page_cost = Cr

3 cpu_tuple_cost = Ct

4 cpu_index_tuple_cost = Ci

5 cpu_operator_cost = Co

6 number of disk pages fetched sequentially = Ns

7 number of disk pages fetched randomly

8 number of tuples proessed

9 number of index entries processed during an index scan

10 number of operations performed

An overall cost is roughly composed of

C = Ns * Cs + Nr * Cr + Nt * Ct + Ni* Ci + No*Co

At the same time, we need to know the default value of the parameter of cost parameters

Cs 1.00

Cr 4.00

Ct 1.00e-2

Ci 5.00e-3

Co 2.50e-3

In general, we can know that a COST is calculated through the steps required for the query, the complexity of the steps, the parallelism of CPU and Imax O, the relation_size of the records of the lock query, and so on. Through this calculation, multi-clock query, the final value comparison, it is concluded that which is a good query, that is abandoned.

Let's do a simple analysis, and here are the default consumption values for each way.

Let's try to calculate the cost of the next query. Look at the figure below.

It is estimated that there will be two questions: where did 1770 come from and why 3600000?

1 1770 is the tuple data element calculated from the overall data, and the other 3600000 is the number of rows of table's records.

You can see that the calculated figure is the number of Query plan.

Generally speaking, it is relatively simple to calculate simple cost manually, but it is more troublesome if complex ones such as parallelism, indexing, and so on. It will be troublesome to compare the price manually.

This is the end of the article on "sample Analysis of PostgreSQL cost Model". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.