PostgreSQL 妙用explain Plan Rows快速估算结果集数量
发布时间:2020-12-13 17:11:06 所属栏目:百科 来源:网络整理
导读:http://people.planetpostgresql.org/dfetter/index.php?/archives/80-Approximate-Counts.html通过这种方法,我们可以快速的估算一个表,视图的记录数,当然也包括带条件的查询中,最终结果的返回集。例如:postgres=# EXPLAIN (FORMAT JSON) SELECT 1 FROM
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 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |