PostgreSQL cost

我们经常执行SQL时，在SQL前加explain加以分析他的执行计划,这个cost不是时间概念，只是评估执行一个SQL的执行代价：

 

pg_tables=# explain select count(*) from cn_ods_tables_2022.order_info_05;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Finalize Aggregate (cost=2029221.15..2029221.16 rows=1 width=8)
-> Gather (cost=2029220.43..2029221.14 rows=7 width=8)
Workers Planned: 7
-> Partial Aggregate (cost=2028220.43..2028220.44 rows=1 width=8)
-> Parallel Seq Scan on order_info_05 (cost=0.00..2022922.34 rows=2119234 width=0)
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
(8 rows)

 

 

postgres=# select name,setting from pg_settings where name like '%cost%' and name not like '%vacuum%';
name | setting
-------------------------+---------
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
jit_above_cost | 100000
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
parallel_setup_cost | 1000
parallel_tuple_cost | 0.1
random_page_cost | 4
seq_page_cost | 1
(10 rows)

postgres=# select relpages,reltuples from pg_class where relname = 'order_info_05';
relpages | reltuples
----------+---------------
1609613 | 1.0178475e+07
(1 row)

从上述可以得知：

(1)order_info_05表有14706050条数据分布在1609613 磁盘页上；
(2)评估时间是（磁盘页*seq_page_cost）+（扫描行*cpu_tuple_cost）;
(3)默认seq_page_cost是1.0，cpu_tuple_cost是0.01，所以评估值是( 1609613 * 1.0) + (14706050 * 0.01) = 1,756,673.5