妙用explain Plan Rows快速估算行 - 分页数估算

背景

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

例子，

CREATE OR REPLACE FUNCTION countit(text)                    
RETURNS float4           
LANGUAGE plpgsql AS          
$$DECLARE               
    v_plan json;                
BEGIN                      
    EXECUTE 'EXPLAIN (FORMAT JSON) '||$1                                
        INTO v_plan;                                                                       
    RETURN v_plan #>> '{0,Plan,"Plan Rows"}';  
END;  
$$;  

postgres=# create table t1234(id int, info text);  
CREATE TABLE  

postgres=# insert into t1234 select generate_series(1,1000000),'test';  
INSERT 0 1000000  

postgres=# analyze t1234;  
ANALYZE  

postgres=# select countit('select * from t1234 where id<1000');  
 countit   
---------  
     954  
(1 row)  

postgres=# select countit('select * from t1234 where id between 1 and 1000 or (id between 100000 and 101000)');  
 countit   
---------  
    1931  
(1 row)  

参考

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

digoal’s 大量PostgreSQL文章入口