Re: explain plans with information about (modified) gucs

Hello Sergei,

> This patch correlates with my proposal
> "add session information column to pg_stat_statements"
> https://www.postgresql.org/message-id/3aa097d7-7c47-187b-5913-db8366cd4491%40gmail.com
> 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;
QUERY PLAN
---------------------------------
[ +
{ +
"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).

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services