?PostgreSQL对空表的行估算

1280阅读　0评论2018-08-20　skykiker
分类：Mysql/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)

  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)

  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)


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

点击(此处)折叠或打开

  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)

  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)



上一篇：PostgreSQL逻辑订阅处理流程解析
下一篇：citus实战系列之一入门篇