Pivotal Knowledge Base

Follow

How to execute explain plan inside a function?

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Purpose

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.

Procedure

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();
  • For 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)

 

Comments

  • Avatar
    Brendan Stephens

    Fantastic idea -- this should work for most generic functions.
    We may need to generate another function to analyze other function bodies to find all the parameters and call them out for complicated scenarios.

Powered by Zendesk