Re: explain plans with information about (modified) gucs - Mailing list pgsql-hackers

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