242
PostgreSQL 妙用explain Plan Rows快速估算结果集数量
https://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 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 的方法，还可以适用没有统计信息的情况。
[参考]
https://people.planetpostgresql.org/dfetter/index.php?/archives/80-Approximate-Counts.html
最后更新：2017-04-01 13:37:08