?PostgreSQL对空表的行估算

PostgreSQL对空表的行估算逻辑挺奇怪的，固定认为有2000多行。

点击(此处)折叠或打开

1. postgres=# create table tbchj(id int);
   
2. CREATE TABLE
   
3. postgres=# explain select * from tbchj;
   
4.                        QUERY PLAN
   
5. ---------------------------------------------------------
   
6.  Seq Scan on tbchj (cost=0.00..35.50 rows=2550 width=4)
   
7. (1 row)
   
8. 
   
9. postgres=# explain select * from tbchj where id =9;
   
10.                       QUERY PLAN
    
11. -------------------------------------------------------
    
12.  Seq Scan on tbchj (cost=0.00..41.88 rows=13 width=4)
    
13.    Filter: (id = 9)
    
14. (2 rows)
    
15. 
    
16. postgres=# analyze tbchj;
    
17. ANALYZE
    
18. postgres=# explain select * from tbchj;
    
19.                        QUERY PLAN
    
20. ---------------------------------------------------------
    
21.  Seq Scan on tbchj (cost=0.00..35.50 rows=2550 width=4)
    
22. (1 row)
    
23. 
    

插入数据后，行估算才能调整正确

点击(此处)折叠或打开

1. postgres=# insert into tbchj values(1);
   
2. INSERT 0 1
   
3. postgres=# explain select * from tbchj;
   
4.                        QUERY PLAN
   
5. ---------------------------------------------------------
   
6.  Seq Scan on tbchj (cost=0.00..35.50 rows=2550 width=4)
   
7. (1 row)
   
8. 
   
9. postgres=# analyze tbchj;
   
10. ANALYZE
    
11. postgres=# explain select * from tbchj;
    
12.                      QUERY PLAN
    
13. -----------------------------------------------------
    
14.  Seq Scan on tbchj (cost=0.00..1.01 rows=1 width=4)
    
15. (1 row)