PostgreSQL 两种方式快速估算表的行数
PostgreSQL About 1,057 wordsSQL
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.
参考
Views: 652 · Posted: 2024-07-02
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...