PostgreSQL Execution Plan Analysis

Someone recently proposed to view the Postgresql execution plan. The following describes how the cost and other related values in the PG execution plan are calculated:
PG version 9.1.2

1. Use the terminal tool PGADMIN to press F7 to execute the statement, and then view the data output and explanation.

2. Command Line Analysis: explain select * from table_name;

Generally, we will pay more attention to the cost and scan methods, such as index or full scan full table scan. When the COST value consumption is large, pay attention to the possibility of optimization.
For the parameters related to the execution plan, see the following example:

Kenyon = # select count (1) from dba. website; -- General stack table, no index constraint count ------- 20 (1 row) kenyon = # explain select * from dba. website; query plan -------------------------------------------------------- Seq Scan on website (cost = 0. 00 .. 1.20 rows = 20 width = 4) (1 row) -- relpages disk page, reltuples is the number of rows (not necessarily consistent with the actual, generally slightly smaller) kenyon = # select relpages, reltuples from pg_class where relname = 'website'; relpages | reltuples -------- -- + ----------- 1 | 20 (1 row) kenyon = # select 1*1 + 20*0.01; -- cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost? Column? ---------- 1.20 (1 row) kenyon = # show tables; cpu_tuple_cost -------------- 0.01 (1 row) kenyon = # show seq_page_cost; seq_page_cost ------------- 1 (1 row) -- add the execution plan with restrictions: kenyon = # select count (1) from dba. website where hits> 15; count ------- 5 (1 row) kenyon = # explain select * from dba. website where hits> 15; query plan ------------------------------------------------------- Seq Scan on website (co St = 0. 00 .. 1.25 rows = 5 width = 4) Filter: (hits> 15) (2 rows) kenyon = # show cpu_operator_cost; cpu_operator_cost --------------- 0.0025 (1 row) because the total number of scans is 20 rows, unchanged, so COST will not drop. On the contrary, it will increase by 0.05 because it consumes additional CPU time to check data that meets the constraints, that is to say, cost adds 20*0.0025 = 0.05 (reltuples * cpu_operator_cost) based on the original one-the execution plan for adding indexes, kenyon = # select count (1) from dba. website_2; count ------- 8000 (1 row) kenyon = # explain select * from dba. website_2; Query plan -------------------------------------------------------------- Seq Scan on website_2 (cost = 0. 00 .. 112.00 rows = 8000 width = 4) (1 row) kenyon = # select relpages, reltuples from pg_class where relname = 'website _ 2 '; relpages | reltuples ---------- + ----------- 32 | 8000 (1 row) kenyon = # explain select * from dba. website_2 where hits> 7900; -- query plan ----------------------------------------- ----------------------------------------- Index Scan using ind_website_2 on website_2 (cost = 0. 00 .. 10.00 rows = 100 width = 4) Index Cond: (hits> 7900) (2 rows) () kenyon = # explain select * from dba. website_2 where hits> 10; -- query plan ---------------------------------------------------- Seq scan on website_2 (cost = 0. 00 .. 132.00 rows = 7991 width = 4) -- 132 = 112 + 8000*0.00 25 Filter: (hits> 10) (2 rows) although the read COST is larger, but because of the index, the accessed data volume decreases, so the overall COST is decreased.

-- Example of an execution plan for multi-table JOIN: If you want to view the actual execution time, you can add the analyze parameter.

kenyon=# explain analyze select * from dba.website a ,dba.website_2 b where a.hits = b.hits and a.hits >18;                                              QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=1.26..1.90 rows=2 width=8) (actual time=0.070..0.075 rows=2 loops=1)   Merge Cond: (b.hits = a.hits)   -> Index Scan using ind_website_2 on website_2 b (cost=0.00..235.25 rows=8000 width=4) (actual time=0.013..0.020 rows=21 loops=1)   -> Sort (cost=1.26..1.26 rows=2 width=4) (actual time=0.035..0.037 rows=2 loops=1)      Sort Key: a.hits      Sort Method: quicksort Memory: 17kB      -> Seq Scan on website a (cost=0.00..1.25 rows=2 width=4) (actual time=0.009..0.011 rows=2 loops=1)       Filter: (hits > 18) Total runtime : 0.120 ms (9 rows) 

Total runtime is the start and end time of the executor, but does not include the time for parsing, rewriting, and planning.
Note: The relpages and reltuples data in pg_class are not updated in real time. Generally, they are updated after vacuum analyze and a few DDL statements (such as index creation.
Example 1:

kenyon=# insert into dba.website select generate_series(8000,9000);INSERT 0 1001kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%'; relpages | reltuples |    relname    | relkind----------+-----------+---------------+---------        1 |        20 | website       | r       32 |      8000 | website_2     | r       20 |      8000 | ind_website_2 | i(3 rows)kenyon=# vacuum analyze dba.website;VACUUMkenyon=# vacuum analyze dba.website;VACUUMkenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%'; relpages | reltuples |    relname    | relkind----------+-----------+---------------+---------        5 |      1021 | website       | r       36 |      8999 | website_2     | r       22 |      8999 | ind_website_2 | i(3 rows)

Example 2:

kenyon=# insert into dba.website select generate_series(8000,9000);INSERT 0 1001kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%'; relpages | reltuples |    relname    | relkind----------+-----------+---------------+---------        1 |        21 | website       | r       36 |      8999 | website_2     | r       22 |      8999 | ind_website_2 | i(3 rows)kenyon=# create index ind_website on dba.website(hits);CREATE INDEXkenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%'; relpages | reltuples |    relname    | relkind----------+-----------+---------------+---------        5 |      1022 | website       | r       36 |      8999 | website_2     | r       22 |      8999 | ind_website_2 | i        5 |      1022 | ind_website   | i(4 rows)

System tables involved:
Pg_stats
Pg_statistic
Pg_class
Pg_stat can be viewed by anyone. It is highly readable and intuitive. pg_statistic can only be read by superusers and has poor readability. It is recommended that you view pg_stats and pg_stats as the pg_statistic view. These two tables are not updated in real time and will be updated when vacuum analyze is required.
System variables involved:
Default_statistics_target
Geqo_threshold
Join_collapse_limit
From_collapse_limit

Kenyon = # show default_statistics_target; default_statistics_target ------------------------- 100 (1 row) kenyon = # show geqo_threshold; -- the size of this parameter sets the critical geqo_threshold -------------- 12 (1 row) kenyon = # show join_collapse_limit of the execution plan from the exhaustive search to the probability selective search; -- join takes the upper limit of execution plan join_collapse_limit ------------------- 8 (1 row) kenyon = # show from_collapse_limit; from_collapse_limit ----------------- 8 (1 row)

EXPLAIN
Name
EXPLAIN-show the execution plan of a statement
Synopsis
EXPLAIN [(option [,...])] statement
EXPLAIN [ANALYZE] [VERBOSE] statement
Where option can be one:
ANALYZE [boolean]
VERBOSE [boolean]
COSTS [boolean]
BUFFERS [boolean]
FORMAT {TEXT | XML | JSON | YAML}

Example:

kenyon=# explain (analyze,verbose,costs,buffers) select id from dba.test222 order by id desc limit 1;                                                          QUERY PLAN                                                         ------------------------------------------------------------------------------------------------------------------------------ Limit  (cost=1807.80..1807.80 rows=1 width=4) (actual time=87.167..87.168 rows=1 loops=1)   Output: id   Buffers: shared hit=393   ->  Sort  (cost=1807.80..2043.60 rows=94320 width=4) (actual time=87.165..87.165 rows=1 loops=1)         Output: id         Sort Key: test222.id         Sort Method: top-N heapsort  Memory: 17kB         Buffers: shared hit=393         ->  Seq Scan on dba.test222  (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.036..42.847 rows=100000 loops=1)               Output: id               Buffers: shared hit=393 Total runtime: 87.183 ms(12 rows)kenyon=# explain (analyze,verbose,costs,buffers) select max(id) from dba.test222;                                                       QUERY PLAN                                                      ------------------------------------------------------------------------------------------------------------------------ Aggregate  (cost=1572.00..1572.01 rows=1 width=4) (actual time=77.679..77.680 rows=1 loops=1)   Output: max(id)   Buffers: shared hit=393   ->  Seq Scan on dba.test222  (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.012..36.908 rows=100000 loops=1)         Output: id         Buffers: shared hit=393 Total runtime: 77.701 ms(7 rows)

Description of the explain parameter:
ANALYZE: Execute the command and display the execution event. The default value is false.
VERBOSE: provides additional information for the execution plan, such as querying field information. The default value is false.
COSTS: displays the execution plan. The default value is true.
BUFFERS: The default value is false. The precondition is analyze.
FORMAT: the default FORMAT is text.