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',,) 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总结
以上是内存溢出为你收集整理的PostgreSQL 妙用explain Plan Rows快速估算结果集数量全部内容,希望文章能够帮你解决PostgreSQL 妙用explain Plan Rows快速估算结果集数量所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)