Subject: Re: explain plans with information about (modified) gucs
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com>
 <10561.1544800904@sss.pgh.pa.us>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <c1282df1-43a8-0854-dfee-790d7dd9c93a@2ndquadrant.com>
Date: Fri, 14 Dec 2018 16:32:42 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.1
MIME-Version: 1.0
In-Reply-To: <10561.1544800904@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk


On 12/14/18 4:21 PM, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.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.

regards

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