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