对于PostgreSQL数据库,可以直接在命令行上使用“explain your_statements”的方式来查看SQL语句的执行计划。
更为具体详细的用法,可以在psql命令行上使用\h explain来查看获取更为具体用法。
这里,需要注意的是,explain analyze 会分析SQL语句的执行计划,并真正执行SQL语句。而,默认情况下,PostgreSQL数据库的事务是自动开启、提交的。那么,对于DML语句来说,如果要使用explain analyze来查看其执行计划的话,就得格外注意了。如下:
postgres=# \c ai ai You are now connected to database "ai" as user "ai". ai=> \d p_table Table "public.p_table" Column | Type | Modifiers --------+-----------------------+----------- id | integer | not null name | character varying(32) | Indexes: "p_table_pkey" PRIMARY KEY, btree (id) Referenced by: TABLE "c_table" CONSTRAINT "c_table_id_fkey" FOREIGN KEY (id) REFERENCES p_table(id) ON UPDATE CASCADE ON DELETE CASCADE ai=> select * from p_table ; id | name ----+------------ 3 | onlyou.com (1 row) ai=> select * from c_table ; id | info ----+-------- 3 | xiamen (1 row) ai=> explain delete from p_table ; QUERY PLAN ---------------------------------------------------------------- Delete on p_table (cost=0.00..17.10 rows=710 width=6) -> Seq Scan on p_table (cost=0.00..17.10 rows=710 width=6) (2 rows) ai=> select * from p_table ; id | name ----+------------ 3 | onlyou.com (1 row) ai=> explain analyze delete from p_table ; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Delete on p_table (cost=0.00..17.10 rows=710 width=6) (actual time=0.074..0.074 rows=0 loops=1) -> Seq Scan on p_table (cost=0.00..17.10 rows=710 width=6) (actual time=0.013..0.014 rows=1 loops=1) Planning time: 0.050 ms Trigger for constraint c_table_id_fkey: time=2.637 calls=1 Execution time: 2.735 ms (5 rows) ai=> select * from p_table ; id | name ----+------ (0 rows) ai=>
explain analyze delete from p_table ;查看一条删除操作SQL的执行计划,其结果就是,真正的把表里的数据删除了,且提交了。
对于所有的DML操作,如果只是想通过explain analyze来查看其执行计划的话,需要在执行之前,显示开启事务,然后查看执行计划,最后回滚事务。
postgres=# begin; BEGIN postgres=# explain analyze xxxxxxxxx; postgres=# rollback; ROLLBACK postgres=#