PostgreSQL 两种方式快速估算表的行数

PostgreSQL About 1,057 words

SQL

EXPLAIN (FORMAT JSON) SELECT 1 FROM my_table;

返回的是一个JSON格式的字段,但explain无法再用select去解构。

[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Async Capable": false,
      "Relation Name": "my_table",
      "Alias": "my_table",
      "Startup Cost": 0.00,
      "Total Cost": 125798.06,
      "Plan Rows": 2987406,
      "Plan Width": 4
    }
  }
]

pg_class

relname指定表名,reltuples表示真实的元组数量(一个元组就是一条记录)

注意:需要执行过analyze命令后pg_classreltuples才会有值。vacuum(包括auto vacuum)会自动执行analyze,所以一般都会有值。

select reltuples from pg_class where relname = 'my_table';

输出

postgres=# select reltuples from pg_class where relname = 'my_table';
  reltuples
--------------
 2.987406e+06
(1 row)

reltuples 含义

Number of live rows in the table. This is only an estimate used by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands such as CREATE INDEX. If the table has never yet been vacuumed or analyzed, reltuples contains -1 indicating that the row count is unknown.

参考

https://www.postgresql.org/docs/16/catalog-pg-class.html

Views: 132 · Posted: 2024-07-02

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓
Today On History
Browsing Refresh