本文共 2875 字，大约阅读时间需要 9 分钟。
http://people.planetpostgresql.org/dfetter/index.php?/archives/80-Approximate-Counts.html通过这种方法，我们可以快速的估算一个表，视图的记录数，当然也包括带条件的查询中，最终结果的返回集。例如：postgres=# EXPLAIN (FORMAT JSON) SELECT 1 FROM t limit 1; QUERY PLAN ------------------------------------------- [ + { + "Plan": { + "Node Type": "Limit", + "Startup Cost": 0.00, + "Total Cost": 0.01, + "Plan Rows": 1, + "Plan Width": 0, + "Plans": [ + { + "Node Type": "Seq Scan", + "Parent Relationship": "Outer",+ "Relation Name": "t", + "Alias": "t", + "Startup Cost": 0.00, + "Total Cost": 14425.00, + "Plan Rows": 1000000, + "Plan Width": 0 + } + ] + } + } + ](1 row)postgres=# EXPLAIN (FORMAT JSON) SELECT 1 FROM t; QUERY PLAN -------------------------------- [ + { + "Plan": { + "Node Type": "Seq Scan",+ "Relation Name": "t", + "Alias": "t", + "Startup Cost": 0.00, + "Total Cost": 14425.00, + "Plan Rows": 1000000, + "Plan Width": 0 + } + } + ](1 row)通过函数，将Plan Rows转换成输出：postgres=# CREATE OR REPLACE FUNCTION countit(name,name) RETURNS float4 LANGUAGE plpgsql AS $$DECLARE v_plan json; BEGIN EXECUTE format('EXPLAIN (FORMAT JSON) SELECT 1 FROM %I.%I', $1,$2) INTO v_plan; RETURN v_plan #>> '{0,Plan,"Plan Rows"}';END;$$;CREATE FUNCTION使用这种方法就可以快速评估所有表和视图的行数了。postgres=# SELECT relname AS "table", CASE WHEN relkind = 'r' THEN reltuples ELSE countit(n.nspname,relname) END AS "approximate_count"FROM pg_catalog.pg_class cJOIN pg_catalog.pg_namespace n ON ( c.relkind IN ('r','v') AND c.relnamespace = n.oid );另外，还有一种更加简便的方法是输出pg_class.reltuples，但是这个字段的值analyze（包括auto analyze）后才有的，而使用explain 的方法，还可以适用没有统计信息的情况。[参考]http://people.planetpostgresql.org/dfetter/index.php?/archives/80-Approximate-Counts.html 转载地址：http://fgkia.baihongyu.com/