PostgreSQL 妙用explain Plan Rows快速估算结果集数量

最新推荐文章于 2024-09-19 11:27:56 发布

wzyzzu

最新推荐文章于 2024-09-19 11:27:56 发布

阅读量2k

点赞数

分类专栏： database

database 专栏收录该内容

39 篇文章 1 订阅

订阅专栏

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",           +
       "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 的方法，还可以适用没有统计信息的情况。

[参考]
http://people.planetpostgresql.org/dfetter/index.php?/archives/80-Approximate-Counts.html