[PG execution Plan] brief introduction of Statistical Information of Postgresql Database execution Plan

-- add analyze actual execution to get the execution plan, but do not add explain analyze select * from test_table;-- only look at the execution path, do not look at costexplain (costs false) select * from test_table;-- to see the cost and buffer hit through the actual execution explain (analyze true,buffers true) select * from test_table -- configuration item enable_seqscan related to the execution plan: whether to select full table scan enable_indexscan: whether to select index scan enable_bitmapscan: whether to select bitmap scan enable_tidscan: whether to tid scan (similar to oracle rowid) enable_nestloop: whether to choose nested circular join enable_hashjoin: for multi-table join, whether to choose hash join enable_mergejoin: for multi-table join, whether to select merge join enable_hashagg: for multi-table join Whether to use hash to aggregate enable_sort: whether to use explicit sorting. -- cost benchmark parameter seq_page_cost: the cost of sequentially accessing one block page at a time in the execution plan. Default 1.0random_page_cost: the cost of randomly accessing a block page, default 4.0cpu_tuple_cost: the cost of processing a data row in the execution plan, and default 0.01cpu_idex_tuple_cost: the cost of processing an index row. Default 0.005cpu_operator_cost: the cost of executing an operator or function, and default 0.0025effective_cache_size: the valid size of the disk buffer available in an index scan in the execution plan. Default 128MB color-gene query optimization: it is an algorithm that uses exploratory search to execute query planning, which can reduce the planning time of load query, and its retrieval is random. Geqo: allow and disable genetic query optimization geqo_threshold: use genetic query optimization only if there are at least so many from relationships involved. Geqo_effort: controls the direct balance between planning time and query planning effectiveness in geqo. Default 5 10geqo_pool_size 1-10geqo_pool_size: control the size of the geqo usage pool geqo_generations: control the number of children used by geqo, the number of iterations of the child meaning algorithm geqo_selection_bias: control the selective preference geqo_seed used by geqo: control the initial value of the random number generator used by geqo to select random paths. -- Statistics collection-- controls whether statistics of the sql execution process are output to the log log _ statment_statslog_parser_statslog_planner_statslog_executor_stats-- receives collection statistics analyze test01 (id2); analyze test01 (id1,id2); analyze test01;-- sets the number of collection statistics rows set default_statistics_target to 500 per column test01;--or sets the statistical target value alter table test01 alter column id2 set statistics 200 -- specify how many unique values there are on this column, alter table test01 alter column id2 set (n_distinct=2000);-- the child table continues to use the parent table's design alter table test01 alter column id2 set (n_distinct_inherited=2000)