PostgreSQL 执行计划与实际成本的偏差

DROP TABLE
postgres=# create table tb11(id integer,info character varying);
CREATE TABLE
postgres=# create table tb12(id integer,info character varying);
CREATE TABLE

postgres=# insert into tb11 select generate_series(1,100),'john' from generate_series(1,10);
INSERT 0 1000
postgres=# insert into tb12 select 1 from generate_series(1,25);
INSERT 0 25

postgres=# explain (analyze,verbose,buffers) select * from tb11,tb12 where tb11.id=tb12.id and tb12.id=1;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..34.12 rows=250 width=45) (actual time=0.022..0.306 rows=250 loops=1)
   Output: tb11.id,tb11.info,tb12.id,tb12.info
   Buffers: shared hit=16
   ->  Seq Scan on public.tb11  (cost=0.00..18.50 rows=10 width=9) (actual time=0.016..0.155 rows=10 loops=1)
         Output: tb11.id,tb11.info
         Filter: (tb11.id = 1)
         Rows Removed by Filter: 990
         Buffers: shared hit=6
   ->  Seq Scan on public.tb12  (cost=0.00..1.31 rows=25 width=36) (actual time=0.001..0.007 rows=25 loops=10)
         Output: tb12.id,tb12.info
         Filter: (tb12.id = 1)
         Buffers: shared hit=10
 Total runtime: 0.361 ms
(13 rows)

digoal=# explain (analyze,buffers,timing,costs) select * from n1,n2 where n1.id=n2.id and n2.id=1;
						QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Nested Loop (cost=0.00..437.00 rows=250 width=45) (actual time=0.042..0.177 rows=10 loops=1) -- 计算嵌套循环总成本时,按照25次循环计算. 所以比实际成本大很多. 实际时间0.177仅仅比0.16+0.01略大. 因为实际只循环一次.
   Output: n1.id,n1.info,n2.id,n2.info
   Buffers: shared hit=3
   ->   Seq Scan on public.n2 (cost=0.00..72.00 rows=25 width=36) (actual time=0.010..0.010 rows=1 loops=1) -- 评估25行,实际1行
          Output: n2.id,n2.info
	  Filter: (n2.id = 1)
	  Buffers: shared hit=1
   -> Seq Scan on public.n1 (cost=0.00..14.50 rows=10 width=9) (actual time=0.027..0.160 rows=10 loops=1) -- 评估需要循环25次,实际循环1次
          Output: n1.id,n1.info
	  Filter: (n1.id = 1)
	  Rows Removed by Filter: 990
	  Buffers: shared hit=2
	  Total runtime: 0.214 ms
(13 rows)

postgres=# create table index_t1(id serial primary key,name character varying);
CREATE TABLE
postgres=# create table index_t2(id serial primary key,name character varying);
CREATE TABLE

postgres=# insert into index_t1 select generate_series(1,10),'john';
INSERT 0 10
postgres=# insert into index_t2 select generate_series(1,100000),'john';
INSERT 0 100000

postgres=# analyze index_t1;
ANALYZE
postgres=# analyze index_t2;
ANALYZE

postgres=# explain (analyze,buffers) select * from index_t1 t1,index_t2 t2 where t1.id=t2.id;
                                                                   QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=1.56..2.01 rows=10 width=18) (actual time=0.018..0.026 rows=10 loops=1)
   Output: t1.id,t1.name,t2.id,t2.name
   Merge Cond: (t2.id = t1.id)
   Buffers: shared hit=4
   ->  Index Scan using index_t2_pkey on public.index_t2 t2  (cost=0.29..3148.29 rows=100000 width=9) (actual time=0.006..0.009 rows=11 loops=1)
         Output: t2.id,t2.name
         Buffers: shared hit=3
   ->  Sort  (cost=1.27..1.29 rows=10 width=9) (actual time=0.008..0.008 rows=10 loops=1)
         Output: t1.id,t1.name
         Sort Key: t1.id
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=1
         ->  Seq Scan on public.index_t1 t1  (cost=0.00..1.10 rows=10 width=9) (actual time=0.001..0.002 rows=10 loops=1)
               Output: t1.id,t1.name
               Buffers: shared hit=1
 Total runtime: 0.050 ms
(16 rows)

postgres=# select 1.29+4148.29*(10/100000);
 ?column? 
----------
     1.29
(1 row)

合并的成本=2.01-1.29=0.72。

来自http://www.postgresql.org/docs/current/static/using-explain.html的原话：

Merge joins also have measurement artifacts that can confuse the unwary. A merge join will stop reading one input if it's exhausted the other input and the next key value in the one input is greater than the last key value of the other input; in such a case there can be no more matches and so no need to scan the rest of the first input. This results in not reading all of one child,with results like those mentioned for LIMIT. Also,if the outer (first) child contains rows with duplicate key values,the inner (second) child is backed up and rescanned for the portion of its rows matching that key value. EXPLAIN ANALYZE counts these repeated emissions of the same inner rows as if they were real additional rows. When there are many outer duplicates,the reported actual row count for the inner child plan node can be significantly larger than the number of rows that are actually in the inner relation.

postgres=# explain (analyze,buffers)select * from tb11 limit 1;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.02 rows=1 width=9) (actual time=0.013..0.013 rows=1 loops=1)
   Output: id,info
   Buffers: shared hit=1
   ->  Seq Scan on public.tb11  (cost=0.00..62.00 rows=4000 width=9) (actual time=0.012..0.012 rows=1 loops=1)
         Output: id,info
         Buffers: shared hit=1
 Total runtime: 0.031 ms
(7 rows)


Time: 0.411 ms

总成本 0.02=62.00*1/4000+limit操作的成本=0.0155+limit操作的成本。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!