EXPLAIN · PHP/Python/前端/Linux 等等學習筆記

[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 ```