PostgreSQL数据库中explain和explain analyze的区别

对于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=#

发表评论

邮箱地址不会被公开。 必填项已用*标注