diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c index edcb91542a..165fe8e4ae 100644 --- a/contrib/auto_explain/auto_explain.c +++ b/contrib/auto_explain/auto_explain.c @@ -30,6 +30,8 @@ static bool auto_explain_log_timing = true; static int auto_explain_log_format = EXPLAIN_FORMAT_TEXT; static bool auto_explain_log_nested_statements = false; static double auto_explain_sample_rate = 1; +static int auto_explain_log_estimate_ratio = -1; +static int auto_explain_log_estimate_min_rows = -1; static const struct config_enum_entry format_options[] = { {"text", EXPLAIN_FORMAT_TEXT, false}, @@ -52,7 +54,9 @@ static ExecutorEnd_hook_type prev_ExecutorEnd = NULL; static bool current_query_sampled = true; #define auto_explain_enabled() \ - (auto_explain_log_min_duration >= 0 && \ + (auto_explain_log_min_duration >= 0 || \ + auto_explain_log_estimate_ratio >= 0 || \ + auto_explain_log_estimate_min_rows >= 0 && \ (nesting_level == 0 || auto_explain_log_nested_statements)) void _PG_init(void); @@ -176,6 +180,31 @@ _PG_init(void) NULL, NULL); + DefineCustomIntVariable("auto_explain.estimate_ratio", + "Planned / returned row ratio.", + NULL, + &auto_explain_log_estimate_ratio, + -1, + -1, INT_MAX / 1000, + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + DefineCustomIntVariable("auto_explain.estimate_min_rows", + "Planned / returned min rows.", + NULL, + &auto_explain_log_estimate_min_rows, + -1, + -1, INT_MAX / 1000, + PGC_SUSET, + 0, + NULL, + NULL, + NULL); + + EmitWarningsOnPlaceholders("auto_explain"); /* Install hooks. */ @@ -309,6 +338,30 @@ explain_ExecutorEnd(QueryDesc *queryDesc) if (queryDesc->totaltime && auto_explain_enabled() && current_query_sampled) { double msec; + int estimate_ratio = -1; + int estimate_rows = -1; + int plan_rows = queryDesc->planstate->plan->plan_rows; + int ntuples = queryDesc->planstate->instrument->ntuples; + + if (plan_rows == 0 && ntuples == 0) + { + estimate_ratio = 1; + } + else if ( ntuples > plan_rows && plan_rows > 0) + { + estimate_ratio = ntuples / plan_rows; + } + else if ( ntuples > 0) + { + estimate_ratio = plan_rows / ntuples; + } + else + { + estimate_ratio = INT_MAX; + } + + estimate_rows = abs(ntuples - plan_rows); + /* * Make sure stats accumulation is done. (Note: it's okay if several @@ -318,7 +371,10 @@ explain_ExecutorEnd(QueryDesc *queryDesc) /* Log plan if duration is exceeded. */ msec = queryDesc->totaltime->total * 1000.0; - if (msec >= auto_explain_log_min_duration) + if ((msec >= auto_explain_log_min_duration && + auto_explain_log_min_duration >= 0 ) || + (estimate_ratio >= auto_explain_log_estimate_ratio && + estimate_rows >= auto_explain_log_estimate_min_rows)) { ExplainState *es = NewExplainState(); @@ -354,8 +410,8 @@ explain_ExecutorEnd(QueryDesc *queryDesc) * often result in duplication. */ ereport(LOG, - (errmsg("duration: %.3f ms plan:\n%s", - msec, es->str->data), + (errmsg("duration: %.3f ms estimate_rows: %d ntuples: %d plan:\n%s", + msec, plan_rows, ntuples, es->str->data), errhidestmt(true))); pfree(es->str->data);