妙用explain Plan Rows快速估算行

Postgres2015全国用户大会将于11月20至21日在北京丽亭华苑酒店召开。本次大会嘉宾阵容强大，国内顶级PostgreSQL数据库专家将悉数到场，并特邀欧洲、俄罗斯、日本、美国等国家和地区的数据库方面专家助阵:

• Postgres-XC项目的发起人铃木市一(SUZUKI Koichi)
• Postgres-XL的项目发起人Mason Sharp
• pgpool的作者石井达夫(Tatsuo Ishii)
• PG-Strom的作者海外浩平(Kaigai Kohei)
• Greenplum研发总监姚延栋
• 周正中(德哥), PostgreSQL中国用户会创始人之一
• 汪洋，平安科技数据库技术部经理
• ……

2015年度PG大象会报名地址：http://postgres2015.eventdove.com/ PostgreSQL中国社区： http://postgres.cn/ PostgreSQL专业1群： 3336901（已满） PostgreSQL专业2群： 100910388 PostgreSQL专业3群： 150657323

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