Re: explain plans with information about (modified) gucs

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Sergei Agalakov <sergei(dot)agalakov(at)gmail(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: explain plans with information about (modified) gucs
Date: 2019-01-14 21:15:37
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers

Hello Sergei,

> This patch correlates with my proposal
> "add session information column to pg_stat_statements"
> They both address the problem to identify the factors that make
> different execution plans for the same SQL statements. You are
> interested in the current settings that affect the execution plan, I'm
> concerned about historical data in pg_stat_statements. From my
> experience the most often offending settings are
> current_schemas/search_path and current_user. Please have in mind that
> probably the same approach that you will use to extend explain plan
> functionality will be eventually implemented to extend
> pg_stat_statements.

Possibly, although I don't have an ambition to export the GUCs into
pg_stat_statements in this patch. There's an issue with merging
different values of GUCs in different executions of a statement, and
it's unclear how to solve that.

> I think that the list of the GUCs that are reported
> by explain plan should be structured like JSON, something like
> extended_settings: { "current_schemas" : ["pg_catalog", "s1", "s2", "public"],
> "current_user" : "user1",
> "enable_nestloop" : "off",
> "work_mem" = "32MB"
> }
> It is less important for yours use case explain, but is important
> for pg_stat_statements case.
> The pg_stat_statements is often accessed by monitoring and reporting
> tools, and it will be a good idea to have > the data here in the
> structured and easily parsed format.

Yes, that's a good point. I think it's fine to keep the current format
for TEXT output, and use a structured format when the explain format is
set to json or yaml. That's what we do for data about Hash nodes, for
example (see show_hash_info).

So I've done that in the attached v5 of the patch, which now produces
something like this:

test=# explain (gucs, format json) select * from t;
[ +
{ +
"Plan": { +
"Node Type": "Seq Scan", +
"Parallel Aware": false, +
"Relation Name": "t", +
"Alias": "t", +
"Startup Cost": 0.00, +
"Total Cost": 61.00, +
"Plan Rows": 2550, +
"Plan Width": 4 +
}, +
"GUC": [ +
"cpu_tuple_cost": "0.02",+
"work_mem": "1GB" +
] +
} +
(1 row)

The one slightly annoying issue is that currently all the options are
formatted as text, including e.g. cpu_tuple_cost. That's because the GUC
options may have show hook, so I can't access the value directly (not
sure if there's an option around it).


Tomas Vondra
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
explain-with-gucs-v5.patch text/x-patch 26.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2019-01-14 21:25:56 Re: COPY FROM WHEN condition
Previous Message Peter Geoghegan 2019-01-14 21:13:30 Non-deterministic IndexTuple toast compression from index_form_tuple() + amcheck false positives