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

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_class的reltuples才会有值。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