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",+
"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 c
JOIN
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
原文链接:https://www.f2er.com/postgresql/194774.html