[TOC] ## EXPLAIN 使用`EXPLAIN`语句显示 PostgreSQL 规划器为指定语句生成的执行计划，以确定该语句是否是一个高效的语句 语法 ``` EXPLAIN [ ( option [, ...] ) ] statement; ``` option 可分为 `ANALYZE`,`VERBOSE `,`COSTS `,`SETTINGS `,`BUFFERS `,`WAL`,`TIMING `,`SUMMARY `,`FORMAT ` ### ANALYZE 选项 执行该命令并显示实际运行时间和其他统计信息 ``` > EXPLAIN ANALYZE SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) (actual time=43.589..43.590 rows=0 loops=1) Index Cond: ((postal_code)::text = 'x'::text) Planning Time: 0.396 ms Execution Time: 43.612 ms ``` ### VERBOSE 显示有关计划的其他信息。 ``` > EXPLAIN VERBOSE SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ------------------------------------------------------------------------------------------------ Index Scan using address_postal_code_idx on public.address (cost=0.28..8.29 rows=1 width=161) Output: address_id, address, address2, district, city_id, postal_code, phone, last_update Index Cond: ((address.postal_code)::text = 'x'::text) ``` ### COSTS 包括有关每个计划节点的估计启动和总成本的信息，以及估计的行数和每行的估计宽度 ``` > EXPLAIN (COSTS) SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ----------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) Index Cond: ((postal_code)::text = 'x'::text) ``` ### SETTINGS 包括有关配置参数的信息。 ``` > EXPLAIN (SETTINGS) SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ----------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) Index Cond: ((postal_code)::text = 'x'::text) ``` ### BUFFERS ``` > EXPLAIN (BUFFERS) SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ----------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) Index Cond: ((postal_code)::text = 'x'::text) ``` ### WAL 包括有关 WAL 记录生成的信息。它必须和 ANALYZE 同时使用。 ``` > EXPLAIN (ANALYZE, WAL) SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) (actual time=0.029..0.029 rows=0 loops=1) Index Cond: ((postal_code)::text = 'x'::text) Planning Time: 0.131 ms Execution Time: 0.043 ms ``` ### TIMING 在输出中包括实际启动时间和在每个节点中花费的时间。它必须和 ANALYZE 同时使用。 ``` > EXPLAIN (ANALYZE, TIMING) SELECT * FROM address WHERE postal_code = 'x'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Index Scan using address_postal_code_idx on address (cost=0.28..8.29 rows=1 width=161) (actual time=0.018..0.018 rows=0 loops=1) Index Cond: ((postal_code)::text = 'x'::text) Planning Time: 0.082 ms Execution Time: 0.030 ms ```