How to Execute "Explain Plan" Inside a Function?

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article shows how to run the explain plan for queries inside functions. This can be useful when SQL is supplied dynamically via a function and there is a need to review the explain plan for the different dynamic values provided.

 

Environment

Resolution

Below are some examples demonstrating how to execute an explain inside a function:

• Drop the function if it already exists:

  DROP FUNCTION explain_in_function();

• Execute the explain plan of the query inside the function while retaining the format:

  CREATE OR REPLACE FUNCTION explain_in_function() 
  returns   varchar 
  AS $BODY$
  DECLARE 
  	plan_collector VARCHAR;
  	plan_line VARCHAR;
  BEGIN 
    plan_collector = ''; 
    for plan_line IN EXECUTE 'explain SELECT a.oid         AS "Tablespace Oid", 
  				       a.spcname     AS "Tablespace name", 
  				       b.fsname      AS "Filespace name", 
  				       c.fselocation AS "Tablespace Location" 
  				FROM   pg_tablespace a, 
  				       pg_filespace b, 
  				       pg_filespace_entry c 
  				WHERE  a.spcfsoid = b.oid 
  				       AND b.oid = c.fsefsoid 
  				ORDER  BY 2, 4' 
    LOOP 
    		plan_collector = plan_collector || e'\n' || plan_line; 
  END LOOP;
  RETURN plan_collector;
  END;
  $BODY$ 
  LANGUAGE plpgsql 
  VOLATILE;

• Run the query to execute the function:

  SELECT explain_in_function();

• Example:

  flightdata=# select explain_in_function();
                                    explain_in_function                                  
  ---------------------------------------------------------------------------------------
                                                                                         
   Sort  (cost=3.17..3.18 rows=4 width=164)                                              
     Sort Key: a.spcname, c.fselocation                                                  
     ->  Hash Join  (cost=2.04..3.14 rows=4 width=164)                                   
           Hash Cond: a.spcfsoid = c.fsefsoid                                            
           ->  Hash Join  (cost=1.02..2.09 rows=4 width=140)                             
                 Hash Cond: a.spcfsoid = b.oid                                           
                 ->  Seq Scan on pg_tablespace a  (cost=0.00..1.02 rows=2 width=72)      
                 ->  Hash  (cost=1.01..1.01 rows=1 width=68)                             
                       ->  Seq Scan on pg_filespace b  (cost=0.00..1.01 rows=1 width=68) 
           ->  Hash  (cost=1.00..1.00 rows=1 width=36)                                   
                 ->  Seq Scan on pg_filespace_entry c  (cost=0.00..1.00 rows=1 width=36) 
   Settings:  optimizer=off                                                              
   Optimizer status: legacy query optimizer
  (1 row)

• If the query accepts a dynamic variable and you are not aware of the filter condition , you can use the same function in a different format for example:

  CREATE OR REPLACE FUNCTION explain_in_function() 
  returns   varchar 
  AS $BODY$
  DECLARE 
     plan_collector VARCHAR;
     plan_line VARCHAR;
     v_date date;
  BEGIN 
    plan_collector = ''; 
    v_date = CURRENT_DATE;
    for plan_line IN EXECUTE 'explain SELECT * FROM pg_stat_last_operation WHERE statime < ''' || v_date ||''';' 
    LOOP 
        plan_collector = plan_collector || e'\n' || plan_line; 
  END LOOP;
  RETURN plan_collector;
  END;
  $BODY$ 
  LANGUAGE plpgsql 
  VOLATILE;

• When executing the function reveals the value along with the explain plan of the query:

  flightdata=# select explain_in_function();
                            explain_in_function                           
  ------------------------------------------------------------------------
                                                                          
   Seq Scan on pg_stat_last_operation  (cost=0.00..2.00 rows=1 width=180) 
     Filter: statime < '2016-06-23 00:00:00+01'::timestamp with time zone 
   Settings:  optimizer=off                                               
   Optimizer status: legacy query optimizer
  (1 row)

Additional Information

+ Environment:

• Pivotal Greenplum 4.3.x
• Operating System- Red Hat Enterprise Linux 6.x