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

This section provides a brief introduction to the cost estimation of sequential scans.

Cost estimation

The database optimizes the query based on the query cost estimation, and the specific operation cost uses the relative cost, such as the default database sequential scanning cost of a database block is 1, and the cost of randomly reading and writing a block is 4. All the operations performed by the executor have corresponding cost functions, such as the cost function of sequential scan is cost_seqscan and that of index scan is cost_index.

In the database, there are three types of costs, namely, start-up cost, operation cost and total cost, in which the total cost = start-up cost + operation cost.

1) Startup cost: refers to the cost before the first tuple is returned. For example, the cost of sequential scanning is the cost of reading the first tuple of the database access target table.

2) operating cost: the cost of extracting all tuples.

3) Total cost: the sum of the above two types of costs.

The following is a detailed explanation of the cost estimate using sequential scan, index scan and sort operations as examples, where the test script is as follows:

DROP TABLE t_tbl

CREATE TABLE t_tbl (id int PRIMARY KEY, value int)

CREATE INDEX idx_t_tbl_value ON t_tbl (value)

INSERT INTO t_tbl SELECT xpencil x from generate_series (1pc10000) x

ANALYZE t_tbl

Datasheet information

Atlasdb=#\ d + t_tbl

Table "public.t_tbl"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

-- +-

Id | integer | | not null | | plain | |

Value | integer | plain | |

Indexes:

"t_tbl_pkey" PRIMARY KEY, btree (id)

"idx_t_tbl_value" btree (value)

Sequential scanning

The sequential scan is estimated by the function cost_seqscan, which is explained by the following query statement.

SELECT FROM t_tbl WHERE id < 5000

The start-up cost of sequential scanning is 0, and the estimation formula of running cost is:

Operation cost = CPU operation cost + disk operation cost

= (CPU tuple processing cost + CPU operation cost) number of tuples + number of sequential scan data block cost blocks

= (cpu_tuple_cost + cpu_operator_cost) TuplesOfTable+seq_page_cost PagesOfTable

The CPU cost of processing each tuple, the CPU operation cost (such as size comparison, conditional filtering, etc.) and the sequential scan block cost are defined by configuration parameters, and the default values are 0.01,0.01,1, respectively. The number of tuples and blocks can be obtained from the query data dictionary, which are 10000 and 45 respectively:

Atlasdb=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'tweetbl'

Relpages | reltuples

-+-

45 | 10000

(1 row)

According to the cost calculation formula, the cost of sequential scanning is:

Operating cost = (0. 01 + 0.0025) 10000 + 1 45 = 170

Looking at the execution plan of the SQL statement, the actual running cost estimated by the database optimizer is 170, which is consistent with the calculation formula:

Atlasdb=# explain SELECT FROM t_tbl WHERE id < 5000

QUERY PLAN

-

Seq Scan on t_tbl (cost=0.00..170.00 rows=4999 width=8)

Filter: (id < 5000)

(2 rows)

As you can see, although with conditional id < 5000, the optimizer still chooses to scan the whole table sequentially.

It is worth noting that if there is no id < 5000, then the operating cost is:

= CPU tuple processing cost tuple + sequential scan data block cost block

Atlasdb=# explain SELECT * FROM t_tbl

QUERY PLAN

-

Seq Scan on t_tbl (cost=0.00..145.00 rows=10000 width=8)

(1 row)

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.