/*------------------------------------------------------------------------- * * explain.c *	  Explain query execution plans * * Portions Copyright (c) 1996-2003, PostgreSQL Global Development Group * Portions Copyright (c) 1994-5, Regents of the University of California * * IDENTIFICATION *	  $Header: /cvsroot/pgsql/src/backend/commands/explain.c,v 1.117 2003/10/17 01:14:26 tgl Exp $ * *------------------------------------------------------------------------- */#include "postgres.h"#include "access/genam.h"#include "access/heapam.h"#include "catalog/pg_type.h"#include "commands/explain.h"#include "commands/prepare.h"#include "executor/executor.h"#include "executor/instrument.h"#include "lib/stringinfo.h"#include "nodes/print.h"#include "optimizer/clauses.h"#include "optimizer/planner.h"#include "optimizer/var.h"#include "parser/parsetree.h"#include "rewrite/rewriteHandler.h"#include "tcop/pquery.h"#include "utils/builtins.h"#include "utils/guc.h"#include "utils/lsyscache.h"typedef struct ExplainState{	/* options */	bool		printCost;		/* print cost */	bool		printNodes;		/* do nodeToString() too */	bool		printAnalyze;	/* print actual times */	/* other states */	List	   *rtable;			/* range table */} ExplainState;static void ExplainOneQuery(Query *query, ExplainStmt *stmt,				TupOutputState *tstate);static double elapsed_time(struct timeval * starttime);static void explain_outNode(StringInfo str,				Plan *plan, PlanState *planstate,				Plan *outer_plan,				int indent, ExplainState *es);static void show_scan_qual(List *qual, bool is_or_qual, const char *qlabel,			   int scanrelid, Plan *outer_plan,			   StringInfo str, int indent, ExplainState *es);static void show_upper_qual(List *qual, const char *qlabel,				const char *outer_name, int outer_varno, Plan *outer_plan,				const char *inner_name, int inner_varno, Plan *inner_plan,				StringInfo str, int indent, ExplainState *es);static void show_sort_keys(List *tlist, int nkeys, AttrNumber *keycols,			   const char *qlabel,			   StringInfo str, int indent, ExplainState *es);static Node *make_ors_ands_explicit(List *orclauses);/* * ExplainQuery - *	  execute an EXPLAIN command */voidExplainQuery(ExplainStmt *stmt, DestReceiver *dest){	Query	   *query = stmt->query;	TupOutputState *tstate;	List	   *rewritten;	List	   *l;	/* prepare for projection of tuples */	tstate = begin_tup_output_tupdesc(dest, ExplainResultDesc(stmt));	if (query->commandType == CMD_UTILITY)	{		/* Rewriter will not cope with utility statements */		if (query->utilityStmt && IsA(query->utilityStmt, DeclareCursorStmt))			ExplainOneQuery(query, stmt, tstate);		else if (query->utilityStmt && IsA(query->utilityStmt, ExecuteStmt))			ExplainExecuteQuery(stmt, tstate);		else			do_text_output_oneline(tstate, "Utility statements have no plan structure");	}	else	{		/* Rewrite through rule system */		rewritten = QueryRewrite(query);		if (rewritten == NIL)		{			/* In the case of an INSTEAD NOTHING, tell at least that */			do_text_output_oneline(tstate, "Query rewrites to nothing");		}		else		{			/* Explain every plan */			foreach(l, rewritten)			{				ExplainOneQuery(lfirst(l), stmt, tstate);				/* put a blank line between plans */				if (lnext(l) != NIL)					do_text_output_oneline(tstate, "");			}		}	}	end_tup_output(tstate);}/* * ExplainResultDesc - *	  construct the result tupledesc for an EXPLAIN */TupleDescExplainResultDesc(ExplainStmt *stmt){	TupleDesc	tupdesc;	/* need a tuple descriptor representing a single TEXT column */	tupdesc = CreateTemplateTupleDesc(1, false);	TupleDescInitEntry(tupdesc, (AttrNumber) 1, "QUERY PLAN",					   TEXTOID, -1, 0, false);	return tupdesc;}/* * ExplainOneQuery - *	  print out the execution plan for one query */static voidExplainOneQuery(Query *query, ExplainStmt *stmt, TupOutputState *tstate){	Plan	   *plan;	QueryDesc  *queryDesc;	bool		isCursor = false;	int			cursorOptions = 0;	/* planner will not cope with utility statements */	if (query->commandType == CMD_UTILITY)	{		if (query->utilityStmt && IsA(query->utilityStmt, DeclareCursorStmt))		{			DeclareCursorStmt *dcstmt;			List	   *rewritten;			dcstmt = (DeclareCursorStmt *) query->utilityStmt;			query = (Query *) dcstmt->query;			isCursor = true;			cursorOptions = dcstmt->options;			/* Still need to rewrite cursor command */			Assert(query->commandType == CMD_SELECT);			rewritten = QueryRewrite(query);			if (length(rewritten) != 1)				elog(ERROR, "unexpected rewrite result");			query = (Query *) lfirst(rewritten);			Assert(query->commandType == CMD_SELECT);			/* do not actually execute the underlying query! */			stmt->analyze = false;		}		else if (query->utilityStmt && IsA(query->utilityStmt, NotifyStmt))		{			do_text_output_oneline(tstate, "NOTIFY");			return;		}		else		{			do_text_output_oneline(tstate, "UTILITY");			return;		}	}	/* plan the query */	plan = planner(query, isCursor, cursorOptions);	/* Create a QueryDesc requesting no output */	queryDesc = CreateQueryDesc(query, plan, None_Receiver, NULL,								stmt->analyze);	ExplainOnePlan(queryDesc, stmt, tstate);}/* * ExplainOnePlan - *		given a planned query, execute it if needed, and then print *		EXPLAIN output * * This is exported because it's called back from prepare.c in the * EXPLAIN EXECUTE case * * Note: the passed-in QueryDesc is freed when we're done with it */voidExplainOnePlan(QueryDesc *queryDesc, ExplainStmt *stmt,			   TupOutputState *tstate){	struct timeval starttime;	double		totaltime = 0;	ExplainState *es;	StringInfo	str;	gettimeofday(&starttime, NULL);	/* call ExecutorStart to prepare the plan for execution */	ExecutorStart(queryDesc, false, !stmt->analyze);	/* Execute the plan for statistics if asked for */	if (stmt->analyze)	{		/* run the plan */		ExecutorRun(queryDesc, ForwardScanDirection, 0L);		/* We can't clean up 'till we're done printing the stats... */		totaltime += elapsed_time(&starttime);	}	es = (ExplainState *) palloc0(sizeof(ExplainState));	es->printCost = true;		/* default */	es->printNodes = stmt->verbose;	es->printAnalyze = stmt->analyze;	es->rtable = queryDesc->parsetree->rtable;	if (es->printNodes)	{		char	   *s;		char	   *f;		s = nodeToString(queryDesc->plantree);		if (s)		{			if (Explain_pretty_print)				f = pretty_format_node_dump(s);			else				f = format_node_dump(s);			pfree(s);			do_text_output_multiline(tstate, f);			pfree(f);			if (es->printCost)				do_text_output_oneline(tstate, "");		/* separator line */		}	}	str = makeStringInfo();	if (es->printCost)	{		explain_outNode(str, queryDesc->plantree, queryDesc->planstate,						NULL, 0, es);	}	/*	 * Close down the query and free resources.  Include time for this in	 * the total runtime.	 */	gettimeofday(&starttime, NULL);	ExecutorEnd(queryDesc);	FreeQueryDesc(queryDesc);	CommandCounterIncrement();	totaltime += elapsed_time(&starttime);	if (es->printCost)	{		if (stmt->analyze)			appendStringInfo(str, "Total runtime: %.3f ms\n",							 1000.0 * totaltime);		do_text_output_multiline(tstate, str->data);	}	pfree(str->data);	pfree(str);	pfree(es);}/* Compute elapsed time in seconds since given gettimeofday() timestamp */static doubleelapsed_time(struct timeval * starttime){	struct timeval endtime;	gettimeofday(&endtime, NULL);	endtime.tv_sec -= starttime->tv_sec;	endtime.tv_usec -= starttime->tv_usec;	while (endtime.tv_usec < 0)	{		endtime.tv_usec += 1000000;		endtime.tv_sec--;	}	return (double) endtime.tv_sec +		(double) endtime.tv_usec / 1000000.0;}/* * explain_outNode - *	  converts a Plan node into ascii string and appends it to 'str' * * planstate points to the executor state node corresponding to the plan node. * We need this to get at the instrumentation data (if any) as well as the * list of subplans. * * outer_plan, if not null, references another plan node that is the outer * side of a join with the current node.  This is only interesting for * deciphering runtime keys of an inner indexscan. */static voidexplain_outNode(StringInfo str,				Plan *plan, PlanState *planstate,				Plan *outer_plan,				int indent, ExplainState *es){	List	   *l;	char	   *pname;	int			i;	if (plan == NULL)	{		appendStringInfoChar(str, '\n');		return;	}	switch (nodeTag(plan))	{		case T_Result:			pname = "Result";			break;		case T_Append:			pname = "Append";			break;		case T_NestLoop:			switch (((NestLoop *) plan)->join.jointype)			{				case JOIN_INNER:					pname = "Nested Loop";					break;				case JOIN_LEFT:					pname = "Nested Loop Left Join";					break;				case JOIN_FULL:					pname = "Nested Loop Full Join";					break;				case JOIN_RIGHT:					pname = "Nested Loop Right Join";					break;				case JOIN_IN:					pname = "Nested Loop IN Join";					break;				default:					pname = "Nested Loop ??? Join";					break;			}			break;		case T_MergeJoin:			switch (((MergeJoin *) plan)->join.jointype)			{				case JOIN_INNER:					pname = "Merge Join";					break;				case JOIN_LEFT:					pname = "Merge Left Join";					break;				case JOIN_FULL:					pname = "Merge Full Join";					break;				case JOIN_RIGHT:					pname = "Merge Right Join";					break;				case JOIN_IN:					pname = "Merge IN Join";					break;				default:					pname = "Merge ??? Join";					break;			}			break;		case T_HashJoin:			switch (((HashJoin *) plan)->join.jointype)			{				case JOIN_INNER:					pname = "Hash Join";					break;				case JOIN_LEFT:					pname = "Hash Left Join";					break;				case JOIN_FULL:					pname = "Hash Full Join";					break;				case JOIN_RIGHT:					pname = "Hash Right Join";					break;				case JOIN_IN:					pname = "Hash IN Join";					break;				default:					pname = "Hash ??? Join";					break;			}			break;		case T_SeqScan:			pname = "Seq Scan";			break;		case T_IndexScan:			pname = "Index Scan";			break;		case T_TidScan:			pname = "Tid Scan";			break;		case T_SubqueryScan:			pname = "Subquery Scan";			break;		case T_FunctionScan:			pname = "Function Scan";			break;		case T_Material:			pname = "Materialize";			break;		case T_Sort:			pname = "Sort";			break;		case T_Group:			pname = "Group";			break;		case T_Agg:			switch (((Agg *) plan)->aggstrategy)			{				case AGG_PLAIN:					pname = "Aggregate";					break;				case AGG_SORTED:					pname = "GroupAggregate";					break;				case AGG_HASHED:					pname = "HashAggregate";					break;				default:					pname = "Aggregate ???";					break;			}			break;		case T_Unique:			pname = "Unique";			break;		case T_SetOp:			switch (((SetOp *) plan)->cmd)			{				case SETOPCMD_INTERSECT:					pname = "SetOp Intersect";					break;				case SETOPCMD_INTERSECT_ALL:					pname = "SetOp Intersect All";					break;				case SETOPCMD_EXCEPT:					pname = "SetOp Except";					break;				case SETOPCMD_EXCEPT_ALL:					pname = "SetOp Except All";					break;				default:					pname = "SetOp ???";					break;			}			break;		case T_Limit:			pname = "Limit";			break;		case T_Hash:			pname = "Hash";			break;		default:			pname = "???";			break;	}	appendStringInfoString(str, pname);	switch (nodeTag(plan))	{		case T_IndexScan:			if (ScanDirectionIsBackward(((IndexScan *) plan)->indxorderdir))				appendStringInfoString(str, " Backward");			appendStringInfoString(str, " using ");			i = 0;			foreach(l, ((IndexScan *) plan)->indxid)			{				Relation	relation;				relation = index_open(lfirsto(l));				appendStringInfo(str, "%s%s",								 (++i > 1) ? ", " : "",					quote_identifier(RelationGetRelationName(relation)));				index_close(relation);			}			/* FALL THRU */		case T_SeqScan:		case T_TidScan:			if (((Scan *) plan)->scanrelid > 0)			{				RangeTblEntry *rte = rt_fetch(((Scan *) plan)->scanrelid,											  es->rtable);				char	   *relname;				/* Assume it's on a real relation */				Assert(rte->rtekind == RTE_RELATION);				/* We only show the rel name, not schema name */				relname = get_rel_name(rte->relid);				appendStringInfo(str, " on %s",								 quote_identifier(relname));				if (strcmp(rte->eref->aliasname, relname) != 0)