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

客官°小女子只卖身不卖艺 2022-08-18 13:23 74阅读 0赞

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