pg_overexplain.c

Go to the documentation of this file.

/*-------------------------------------------------------------------------
 *
 * pg_overexplain.c
 *    allow EXPLAIN to dump even more details
 *
 * Copyright (c) 2016-2025, PostgreSQL Global Development Group
 *
 *    contrib/pg_overexplain/pg_overexplain.c
 *-------------------------------------------------------------------------
 */
#include "postgres.h"
 
#include "catalog/pg_class.h"
#include "commands/defrem.h"
#include "commands/explain.h"
#include "commands/explain_format.h"
#include "commands/explain_state.h"
#include "fmgr.h"
#include "parser/parsetree.h"
#include "storage/lock.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
 
PG_MODULE_MAGIC_EXT(
                    .name = "pg_overexplain",
                    .version = PG_VERSION
);
 
typedef struct
{
    bool        debug;
    bool        range_table;
} overexplain_options;
 
static overexplain_options *overexplain_ensure_options(ExplainState *es);
static void overexplain_debug_handler(ExplainState *es, DefElem *opt,
                                      ParseState *pstate);
static void overexplain_range_table_handler(ExplainState *es, DefElem *opt,
                                            ParseState *pstate);
static void overexplain_per_node_hook(PlanState *planstate, List *ancestors,
                                      const char *relationship,
                                      const char *plan_name,
                                      ExplainState *es);
static void overexplain_per_plan_hook(PlannedStmt *plannedstmt,
                                      IntoClause *into,
                                      ExplainState *es,
                                      const char *queryString,
                                      ParamListInfo params,
                                      QueryEnvironment *queryEnv);
static void overexplain_debug(PlannedStmt *plannedstmt, ExplainState *es);
static void overexplain_range_table(PlannedStmt *plannedstmt,
                                    ExplainState *es);
static void overexplain_alias(const char *qlabel, Alias *alias,
                              ExplainState *es);
static void overexplain_bitmapset(const char *qlabel, Bitmapset *bms,
                                  ExplainState *es);
static void overexplain_intlist(const char *qlabel, List *list,
                                ExplainState *es);
 
static int  es_extension_id;
static explain_per_node_hook_type prev_explain_per_node_hook;
static explain_per_plan_hook_type prev_explain_per_plan_hook;
 
/*
 * Initialization we do when this module is loaded.
 */
void
_PG_init(void)
{
    /* Get an ID that we can use to cache data in an ExplainState. */
    es_extension_id = GetExplainExtensionId("pg_overexplain");
 
    /* Register the new EXPLAIN options implemented by this module. */
    RegisterExtensionExplainOption("debug", overexplain_debug_handler);
    RegisterExtensionExplainOption("range_table",
                                   overexplain_range_table_handler);
 
    /* Use the per-node and per-plan hooks to make our options do something. */
    prev_explain_per_node_hook = explain_per_node_hook;
    explain_per_node_hook = overexplain_per_node_hook;
    prev_explain_per_plan_hook = explain_per_plan_hook;
    explain_per_plan_hook = overexplain_per_plan_hook;
}
 
/*
 * Get the overexplain_options structure from an ExplainState; if there is
 * none, create one, attach it to the ExplainState, and return it.
 */
static overexplain_options *
overexplain_ensure_options(ExplainState *es)
{
    overexplain_options *options;
 
    options = GetExplainExtensionState(es, es_extension_id);
 
    if (options == NULL)
    {
        options = palloc0(sizeof(overexplain_options));
        SetExplainExtensionState(es, es_extension_id, options);
    }
 
    return options;
}
 
/*
 * Parse handler for EXPLAIN (DEBUG).
 */
static void
overexplain_debug_handler(ExplainState *es, DefElem *opt, ParseState *pstate)
{
    overexplain_options *options = overexplain_ensure_options(es);
 
    options->debug = defGetBoolean(opt);
}
 
/*
 * Parse handler for EXPLAIN (RANGE_TABLE).
 */
static void
overexplain_range_table_handler(ExplainState *es, DefElem *opt,
                                ParseState *pstate)
{
    overexplain_options *options = overexplain_ensure_options(es);
 
    options->range_table = defGetBoolean(opt);
}
 
/*
 * Print out additional per-node information as appropriate. If the user didn't
 * specify any of the options we support, do nothing; else, print whatever is
 * relevant to the specified options.
 */
static void
overexplain_per_node_hook(PlanState *planstate, List *ancestors,
                          const char *relationship, const char *plan_name,
                          ExplainState *es)
{
    overexplain_options *options;
    Plan       *plan = planstate->plan;
 
    if (prev_explain_per_node_hook)
        (*prev_explain_per_node_hook) (planstate, ancestors, relationship,
                                       plan_name, es);
 
    options = GetExplainExtensionState(es, es_extension_id);
    if (options == NULL)
        return;
 
    /*
     * If the "debug" option was given, display miscellaneous fields from the
     * "Plan" node that would not otherwise be displayed.
     */
    if (options->debug)
    {
        /*
         * Normal EXPLAIN will display "Disabled: true" if the node is
         * disabled; but that is based on noticing that plan->disabled_nodes
         * is higher than the sum of its children; here, we display the raw
         * value, for debugging purposes.
         */
        ExplainPropertyInteger("Disabled Nodes", NULL, plan->disabled_nodes,
                               es);
 
        /*
         * Normal EXPLAIN will display the parallel_aware flag; here, we show
         * the parallel_safe flag as well.
         */
        ExplainPropertyBool("Parallel Safe", plan->parallel_safe, es);
 
        /*
         * The plan node ID isn't normally displayed, since it is only useful
         * for debugging.
         */
        ExplainPropertyInteger("Plan Node ID", NULL, plan->plan_node_id, es);
 
        /*
         * It is difficult to explain what extParam and allParam mean in plain
         * language, so we simply display these fields labelled with the
         * structure member name. For compactness, the text format omits the
         * display of this information when the bitmapset is empty.
         */
        if (es->format != EXPLAIN_FORMAT_TEXT || !bms_is_empty(plan->extParam))
            overexplain_bitmapset("extParam", plan->extParam, es);
        if (es->format != EXPLAIN_FORMAT_TEXT || !bms_is_empty(plan->allParam))
            overexplain_bitmapset("allParam", plan->allParam, es);
    }
 
    /*
     * If the "range_table" option was specified, display information about
     * the range table indexes for this node.
     */
    if (options->range_table)
    {
        switch (nodeTag(plan))
        {
            case T_SeqScan:
            case T_SampleScan:
            case T_IndexScan:
            case T_IndexOnlyScan:
            case T_BitmapHeapScan:
            case T_TidScan:
            case T_TidRangeScan:
            case T_SubqueryScan:
            case T_FunctionScan:
            case T_TableFuncScan:
            case T_ValuesScan:
            case T_CteScan:
            case T_NamedTuplestoreScan:
            case T_WorkTableScan:
                ExplainPropertyInteger("Scan RTI", NULL,
                                       ((Scan *) plan)->scanrelid, es);
                break;
            case T_ForeignScan:
                overexplain_bitmapset("Scan RTIs",
                                      ((ForeignScan *) plan)->fs_base_relids,
                                      es);
                break;
            case T_CustomScan:
                overexplain_bitmapset("Scan RTIs",
                                      ((CustomScan *) plan)->custom_relids,
                                      es);
                break;
            case T_ModifyTable:
                ExplainPropertyInteger("Nominal RTI", NULL,
                                       ((ModifyTable *) plan)->nominalRelation, es);
                ExplainPropertyInteger("Exclude Relation RTI", NULL,
                                       ((ModifyTable *) plan)->exclRelRTI, es);
                break;
            case T_Append:
                overexplain_bitmapset("Append RTIs",
                                      ((Append *) plan)->apprelids,
                                      es);
                break;
            case T_MergeAppend:
                overexplain_bitmapset("Append RTIs",
                                      ((MergeAppend *) plan)->apprelids,
                                      es);
                break;
            case T_Result:
 
                /*
                 * 'relids' is only meaningful when plan->lefttree is NULL,
                 * but if somehow it ends up set when plan->lefttree is not
                 * NULL, print it anyway.
                 */
                if (plan->lefttree == NULL ||
                    ((Result *) plan)->relids != NULL)
                    overexplain_bitmapset("RTIs",
                                          ((Result *) plan)->relids,
                                          es);
            default:
                break;
        }
    }
}
 
/*
 * Print out additional per-query information as appropriate. Here again, if
 * the user didn't specify any of the options implemented by this module, do
 * nothing; otherwise, call the appropriate function for each specified
 * option.
 */
static void
overexplain_per_plan_hook(PlannedStmt *plannedstmt,
                          IntoClause *into,
                          ExplainState *es,
                          const char *queryString,
                          ParamListInfo params,
                          QueryEnvironment *queryEnv)
{
    overexplain_options *options;
 
    if (prev_explain_per_plan_hook)
        (*prev_explain_per_plan_hook) (plannedstmt, into, es, queryString,
                                       params, queryEnv);
 
    options = GetExplainExtensionState(es, es_extension_id);
    if (options == NULL)
        return;
 
    if (options->debug)
        overexplain_debug(plannedstmt, es);
 
    if (options->range_table)
        overexplain_range_table(plannedstmt, es);
}
 
/*
 * Print out various details from the PlannedStmt that wouldn't otherwise
 * be displayed.
 *
 * We don't try to print everything here. Information that would be displayed
 * anyway doesn't need to be printed again here, and things with lots of
 * substructure probably should be printed via separate options, or not at all.
 */
static void
overexplain_debug(PlannedStmt *plannedstmt, ExplainState *es)
{
    char       *commandType = NULL;
    StringInfoData flags;
 
    /* Even in text mode, we want to set this output apart as its own group. */
    ExplainOpenGroup("PlannedStmt", "PlannedStmt", true, es);
    if (es->format == EXPLAIN_FORMAT_TEXT)
    {
        ExplainIndentText(es);
        appendStringInfoString(es->str, "PlannedStmt:\n");
        es->indent++;
    }
 
    /* Print the command type. */
    switch (plannedstmt->commandType)
    {
        case CMD_UNKNOWN:
            commandType = "unknown";
            break;
        case CMD_SELECT:
            commandType = "select";
            break;
        case CMD_UPDATE:
            commandType = "update";
            break;
        case CMD_INSERT:
            commandType = "insert";
            break;
        case CMD_DELETE:
            commandType = "delete";
            break;
        case CMD_MERGE:
            commandType = "merge";
            break;
        case CMD_UTILITY:
            commandType = "utility";
            break;
        case CMD_NOTHING:
            commandType = "nothing";
            break;
    }
    ExplainPropertyText("Command Type", commandType, es);
 
    /* Print various properties as a comma-separated list of flags. */
    initStringInfo(&flags);
    if (plannedstmt->hasReturning)
        appendStringInfoString(&flags, ", hasReturning");
    if (plannedstmt->hasModifyingCTE)
        appendStringInfoString(&flags, ", hasModifyingCTE");
    if (plannedstmt->canSetTag)
        appendStringInfoString(&flags, ", canSetTag");
    if (plannedstmt->transientPlan)
        appendStringInfoString(&flags, ", transientPlan");
    if (plannedstmt->dependsOnRole)
        appendStringInfoString(&flags, ", dependsOnRole");
    if (plannedstmt->parallelModeNeeded)
        appendStringInfoString(&flags, ", parallelModeNeeded");
    if (flags.len == 0)
        appendStringInfoString(&flags, ", none");
    ExplainPropertyText("Flags", flags.data + 2, es);
 
    /* Various lists of integers. */
    overexplain_bitmapset("Subplans Needing Rewind",
                          plannedstmt->rewindPlanIDs, es);
    overexplain_intlist("Relation OIDs",
                        plannedstmt->relationOids, es);
    overexplain_intlist("Executor Parameter Types",
                        plannedstmt->paramExecTypes, es);
 
    /*
     * Print the statement location. (If desired, we could alternatively print
     * stmt_location and stmt_len as two separate fields.)
     */
    if (plannedstmt->stmt_location == -1)
        ExplainPropertyText("Parse Location", "Unknown", es);
    else if (plannedstmt->stmt_len == 0)
        ExplainPropertyText("Parse Location",
                            psprintf("%d to end", plannedstmt->stmt_location),
                            es);
    else
        ExplainPropertyText("Parse Location",
                            psprintf("%d for %d bytes",
                                     plannedstmt->stmt_location,
                                     plannedstmt->stmt_len),
                            es);
 
    /* Done with this group. */
    if (es->format == EXPLAIN_FORMAT_TEXT)
        es->indent--;
    ExplainCloseGroup("PlannedStmt", "PlannedStmt", true, es);
}
 
/*
 * Provide detailed information about the contents of the PlannedStmt's
 * range table.
 */
static void
overexplain_range_table(PlannedStmt *plannedstmt, ExplainState *es)
{
    Index       rti;
 
    /* Open group, one entry per RangeTblEntry */
    ExplainOpenGroup("Range Table", "Range Table", false, es);
 
    /* Iterate over the range table */
    for (rti = 1; rti <= list_length(plannedstmt->rtable); ++rti)
    {
        RangeTblEntry *rte = rt_fetch(rti, plannedstmt->rtable);
        char       *kind = NULL;
        char       *relkind;
 
        /* NULL entries are possible; skip them */
        if (rte == NULL)
            continue;
 
        /* Translate rtekind to a string */
        switch (rte->rtekind)
        {
            case RTE_RELATION:
                kind = "relation";
                break;
            case RTE_SUBQUERY:
                kind = "subquery";
                break;
            case RTE_JOIN:
                kind = "join";
                break;
            case RTE_FUNCTION:
                kind = "function";
                break;
            case RTE_TABLEFUNC:
                kind = "tablefunc";
                break;
            case RTE_VALUES:
                kind = "values";
                break;
            case RTE_CTE:
                kind = "cte";
                break;
            case RTE_NAMEDTUPLESTORE:
                kind = "namedtuplestore";
                break;
            case RTE_RESULT:
                kind = "result";
                break;
            case RTE_GROUP:
                kind = "group";
                break;
        }
 
        /* Begin group for this specific RTE */
        ExplainOpenGroup("Range Table Entry", NULL, true, es);
 
        /*
         * In text format, the summary line displays the range table index and
         * rtekind, plus indications if rte->inh and/or rte->inFromCl are set.
         * In other formats, we display those as separate properties.
         */
        if (es->format == EXPLAIN_FORMAT_TEXT)
        {
            ExplainIndentText(es);
            appendStringInfo(es->str, "RTI %u (%s%s%s):\n", rti, kind,
                             rte->inh ? ", inherited" : "",
                             rte->inFromCl ? ", in-from-clause" : "");
            es->indent++;
        }
        else
        {
            ExplainPropertyUInteger("RTI", NULL, rti, es);
            ExplainPropertyText("Kind", kind, es);
            ExplainPropertyBool("Inherited", rte->inh, es);
            ExplainPropertyBool("In From Clause", rte->inFromCl, es);
        }
 
        /* rte->alias is optional; rte->eref is requested */
        if (rte->alias != NULL)
            overexplain_alias("Alias", rte->alias, es);
        overexplain_alias("Eref", rte->eref, es);
 
        /*
         * We adhere to the usual EXPLAIN convention that schema names are
         * displayed only in verbose mode, and we emit nothing if there is no
         * relation OID.
         */
        if (rte->relid != 0)
        {
            const char *relname;
            const char *qualname;
 
            relname = quote_identifier(get_rel_name(rte->relid));
 
            if (es->verbose)
            {
                Oid         nspoid = get_rel_namespace(rte->relid);
                char       *nspname;
 
                nspname = get_namespace_name_or_temp(nspoid);
                qualname = psprintf("%s.%s", quote_identifier(nspname),
                                    relname);
            }
            else
                qualname = relname;
 
            ExplainPropertyText("Relation", qualname, es);
        }
 
        /* Translate relkind, if any, to a string */
        switch (rte->relkind)
        {
            case RELKIND_RELATION:
                relkind = "relation";
                break;
            case RELKIND_INDEX:
                relkind = "index";
                break;
            case RELKIND_SEQUENCE:
                relkind = "sequence";
                break;
            case RELKIND_TOASTVALUE:
                relkind = "toastvalue";
                break;
            case RELKIND_VIEW:
                relkind = "view";
                break;
            case RELKIND_MATVIEW:
                relkind = "matview";
                break;
            case RELKIND_COMPOSITE_TYPE:
                relkind = "composite_type";
                break;
            case RELKIND_FOREIGN_TABLE:
                relkind = "foreign_table";
                break;
            case RELKIND_PARTITIONED_TABLE:
                relkind = "partitioned_table";
                break;
            case RELKIND_PARTITIONED_INDEX:
                relkind = "partitioned_index";
                break;
            case '\0':
                relkind = NULL;
                break;
            default:
                relkind = psprintf("%c", rte->relkind);
                break;
        }
 
        /* If there is a relkind, show it */
        if (relkind != NULL)
            ExplainPropertyText("Relation Kind", relkind, es);
 
        /* If there is a lock mode, show it */
        if (rte->rellockmode != 0)
            ExplainPropertyText("Relation Lock Mode",
                                GetLockmodeName(DEFAULT_LOCKMETHOD,
                                                rte->rellockmode), es);
 
        /*
         * If there is a perminfoindex, show it. We don't try to display
         * information from the RTEPermissionInfo node here because they are
         * just indexes plannedstmt->permInfos which could be separately
         * dumped if someone wants to add EXPLAIN (PERMISSIONS) or similar.
         */
        if (rte->perminfoindex != 0)
            ExplainPropertyInteger("Permission Info Index", NULL,
                                   rte->perminfoindex, es);
 
        /*
         * add_rte_to_flat_rtable will clear rte->tablesample and
         * rte->subquery in the finished plan, so skip those fields.
         *
         * However, the security_barrier flag is not shown by the core code,
         * so let's print it here.
         */
        if (es->format != EXPLAIN_FORMAT_TEXT || rte->security_barrier)
            ExplainPropertyBool("Security Barrier", rte->security_barrier, es);
 
        /*
         * If this is a join, print out the fields that are specifically valid
         * for joins.
         */
        if (rte->rtekind == RTE_JOIN)
        {
            char       *jointype;
 
            switch (rte->jointype)
            {
                case JOIN_INNER:
                    jointype = "Inner";
                    break;
                case JOIN_LEFT:
                    jointype = "Left";
                    break;
                case JOIN_FULL:
                    jointype = "Full";
                    break;
                case JOIN_RIGHT:
                    jointype = "Right";
                    break;
                case JOIN_SEMI:
                    jointype = "Semi";
                    break;
                case JOIN_ANTI:
                    jointype = "Anti";
                    break;
                case JOIN_RIGHT_SEMI:
                    jointype = "Right Semi";
                    break;
                case JOIN_RIGHT_ANTI:
                    jointype = "Right Anti";
                    break;
                default:
                    jointype = "???";
                    break;
            }
 
            /* Join type */
            ExplainPropertyText("Join Type", jointype, es);
 
            /* # of JOIN USING columns */
            if (es->format != EXPLAIN_FORMAT_TEXT || rte->joinmergedcols != 0)
                ExplainPropertyInteger("JOIN USING Columns", NULL,
                                       rte->joinmergedcols, es);
 
            /*
             * add_rte_to_flat_rtable will clear joinaliasvars, joinleftcols,
             * joinrightcols, and join_using_alias here, so skip those fields.
             */
        }
 
        /*
         * add_rte_to_flat_rtable will clear functions, tablefunc, and
         * values_lists, but we can display funcordinality.
         */
        if (rte->rtekind == RTE_FUNCTION)
            ExplainPropertyBool("WITH ORDINALITY", rte->funcordinality, es);
 
        /*
         * If this is a CTE, print out CTE-related properties.
         */
        if (rte->rtekind == RTE_CTE)
        {
            ExplainPropertyText("CTE Name", rte->ctename, es);
            ExplainPropertyUInteger("CTE Levels Up", NULL, rte->ctelevelsup,
                                    es);
            ExplainPropertyBool("CTE Self-Reference", rte->self_reference, es);
        }
 
        /*
         * add_rte_to_flat_rtable will clear coltypes, coltypmods, and
         * colcollations, so skip those fields.
         *
         * If this is an ephemeral named relation, print out ENR-related
         * properties.
         */
        if (rte->rtekind == RTE_NAMEDTUPLESTORE)
        {
            ExplainPropertyText("ENR Name", rte->enrname, es);
            ExplainPropertyFloat("ENR Tuples", NULL, rte->enrtuples, 0, es);
        }
 
        /*
         * add_rte_to_flat_rtable will clear groupexprs and securityQuals, so
         * skip that field. We have handled inFromCl above, so the only thing
         * left to handle here is rte->lateral.
         */
        if (es->format != EXPLAIN_FORMAT_TEXT || rte->lateral)
            ExplainPropertyBool("Lateral", rte->lateral, es);
 
        /* Done with this RTE */
        if (es->format == EXPLAIN_FORMAT_TEXT)
            es->indent--;
        ExplainCloseGroup("Range Table Entry", NULL, true, es);
    }
 
    /* Print PlannedStmt fields that contain RTIs. */
    if (es->format != EXPLAIN_FORMAT_TEXT ||
        !bms_is_empty(plannedstmt->unprunableRelids))
        overexplain_bitmapset("Unprunable RTIs", plannedstmt->unprunableRelids,
                              es);
    if (es->format != EXPLAIN_FORMAT_TEXT ||
        plannedstmt->resultRelations != NIL)
        overexplain_intlist("Result RTIs", plannedstmt->resultRelations, es);
 
    /* Close group, we're all done */
    ExplainCloseGroup("Range Table", "Range Table", false, es);
}
 
/*
 * Emit a text property describing the contents of an Alias.
 *
 * Column lists can be quite long here, so perhaps we should have an option
 * to limit the display length by # of column or # of characters, but for
 * now, just display everything.
 */
static void
overexplain_alias(const char *qlabel, Alias *alias, ExplainState *es)
{
    StringInfoData buf;
    bool        first = true;
 
    Assert(alias != NULL);
 
    initStringInfo(&buf);
    appendStringInfo(&buf, "%s (", quote_identifier(alias->aliasname));
 
    foreach_node(String, cn, alias->colnames)
    {
        appendStringInfo(&buf, "%s%s",
                         first ? "" : ", ",
                         quote_identifier(cn->sval));
        first = false;
    }
 
    appendStringInfoChar(&buf, ')');
    ExplainPropertyText(qlabel, buf.data, es);
    pfree(buf.data);
}
 
/*
 * Emit a text property describing the contents of a bitmapset -- either a
 * space-separated list of integer members, or the word "none" if the bitmapset
 * is empty.
 */
static void
overexplain_bitmapset(const char *qlabel, Bitmapset *bms, ExplainState *es)
{
    int         x = -1;
 
    StringInfoData buf;
 
    if (bms_is_empty(bms))
    {
        ExplainPropertyText(qlabel, "none", es);
        return;
    }
 
    initStringInfo(&buf);
    while ((x = bms_next_member(bms, x)) >= 0)
        appendStringInfo(&buf, " %d", x);
    Assert(buf.data[0] == ' ');
    ExplainPropertyText(qlabel, buf.data + 1, es);
    pfree(buf.data);
}
 
/*
 * Emit a text property describing the contents of a list of integers, OIDs,
 * or XIDs -- either a space-separated list of integer members, or the word
 * "none" if the list is empty.
 */
static void
overexplain_intlist(const char *qlabel, List *list, ExplainState *es)
{
    StringInfoData buf;
 
    initStringInfo(&buf);
 
    if (list == NIL)
    {
        ExplainPropertyText(qlabel, "none", es);
        return;
    }
 
    if (IsA(list, IntList))
    {
        foreach_int(i, list)
            appendStringInfo(&buf, " %d", i);
    }
    else if (IsA(list, OidList))
    {
        foreach_oid(o, list)
            appendStringInfo(&buf, " %u", o);
    }
    else if (IsA(list, XidList))
    {
        foreach_xid(x, list)
            appendStringInfo(&buf, " %u", x);
    }
    else
    {
        appendStringInfoString(&buf, " not an integer list");
        Assert(false);
    }
 
    if (buf.len > 0)
        ExplainPropertyText(qlabel, buf.data + 1, es);
 
    pfree(buf.data);
}