Re: explain plans with information about (modified) gucs

From: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: explain plans with information about (modified) gucs
Date: 2018-12-15 15:44:48
Message-ID: 631ae5c8-5581-6c5e-382c-2dcdb2e5c956@2ndquadrant.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 12/14/18 4:32 PM, Tomas Vondra wrote:
>
>
> On 12/14/18 4:21 PM, Tom Lane wrote:
>> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>>> ... I propose to extend EXPLAIN output with an additional option, which
>>> would include information about modified GUCs in the execution plan
>>> (disabled by default, of course):
>>
>> I'm a bit suspicious about whether this'll have any actual value,
>> if it's disabled by default (which I agree it needs to be, if only for
>> compatibility reasons). The problem you're trying to solve is basically
>> "I forgot that this might have an effect", but stuff that isn't shown
>> by default will not help you un-forget. It certainly won't fix the
>> form of the problem that I run into, which is people sending in EXPLAIN
>> plans and not mentioning their weird local settings.
>>
>
> Not quite.
>
> I agree we'll still have to deal with plans from users without this
> info, but it's easier to ask for explain with this extra option (just
> like we regularly ask for explain analyze instead of just plain
> explain). I'd expect the output to be more complete than trying to
> figure out which of the GUCs might have effect / been modified here.
>
> But more importantly - my personal primary use case here is explains
> from application connections generated using auto_explain, with some
> application-level GUC magic. And there I can easily tweak auto_explain
> config to do (auto_explain.log_gucs = true) of course.
>
>>> We certainly don't want to include all GUCs, so the question is how to
>>> decide which GUCs are interesting. The simplest approach would be to
>>> look for GUCs that changed in the session (source == PGC_S_SESSION), but
>>> that does not help with SET SESSION AUTHORIZATION / ROLE cases. So we
>>> probably want (source > PGC_S_ARGV), but probably not PGC_S_OVERRIDE
>>> because that includes irrelevant options like wal_buffers etc.
>>
>> Don't you want to show anything that's not the built-in default?
>> (I agree OVERRIDE could be excluded, but that's irrelevant for query
>> tuning parameters.) Just because somebody injected a damfool setting
>> of, say, random_page_cost via the postmaster command line or
>> environment settings doesn't make it not damfool :-(
>>
>
> Probably. My assumption here was that I can do
>
> select * from pg_settings
>
> and then combine it with whatever is included in the plan. But you're
> right comparing it with the built-in default may be a better option.
>

FWIW here is a v3 of the patch, using the built-in default, and fixing a
silly thinko resulting in the code not being executed from auto_explain.

regards

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

Attachment Content-Type Size
explain-with-gucs-v3.patch text/x-patch 7.5 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-12-15 15:45:21 Re: Variable-length FunctionCallInfoData
Previous Message Peter Eisentraut 2018-12-15 15:21:19 Re: New function pg_stat_statements_reset_query() to reset statistics of a specific query