PostgreSQL 常用维护操作(二)
发布时间:2020-12-13 17:51:29 所属栏目:百科 来源:网络整理
导读:1.使用命令行方式查看用户自己的表。 [postgres@kevin root]$ psql -c "d" -d kevin_testcould not change directory to "/root" List of relations Schema | Name | Type | Owner--------+---------------+-------+---------------- public | tbl_test_por
1.使用命令行方式查看用户自己的表。 [postgres@kevin root]$ psql -c "d" -d kevin_test could not change directory to "/root" List of relations Schema | Name | Type | Owner --------+---------------+-------+---------------- public | tbl_test_port | table | pg_test_user_3 (1 row) [postgres@kevin root]$
kevin_test=# SELECT pg_database_size(current_database()); pg_database_size ------------------ 5431588 (1 row) kevin_test=#
kevin_test=# SELECT datname,pg_database_size(datname) from pg_database; datname | pg_database_size --------------------+------------------ template1 | 5316612 template0 | 5316612 postgres | 5415204 kevin_test | 5431588 pg_test_database_3 | 5316612 (5 rows) kevin_test=#
a. SQL方式查询 kevin_test=# SELECT pg_relation_size('tbl_test_port'); pg_relation_size ------------------ 8192 (1 row) kevin_test=# b. 命令方式查询 kevin_test=# dt+ tbl_test_port List of relations Schema | Name | Type | Owner | Size | Description --------+---------------+-------+----------------+------------+------------- public | tbl_test_port | table | pg_test_user_3 | 8192 bytes | (1 row) kevin_test=#
kevin_test=# SELECT pg_total_relation_size('tbl_test_port'); pg_total_relation_size ------------------------ 24576 (1 row) kevin_test=#
kevin_test=# SELECT count(*) from tbl_test_port; count ------- 12 (1 row) kevin_test=# kevin_test=# SELECT (CASE WHEN reltuples > 0 THEN pg_relation_size('tbl_test_port') / (8192 * relpages / reltuples) ELSE 0 END)::bigint as estimated_row_count FROM pg_class WHERE oid = 'tbl_test_port'::regclass; estimated_row_count --------------------- 0 (1 row) kevin_test=# 可以看出,在记录个数较少的情况下,结果不准确。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |