Re: Default gucs for EXPLAIN

From: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
To: Vik Fearing <vik(at)postgresfriends(dot)org>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>, PostgreSQL Developers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Default gucs for EXPLAIN
Date: 2020-05-24 07:31:47
Message-ID: alpine.DEB.2.22.394.2005240910330.463947@pseudo
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Bonjour Vik,

>> Do we really want default_explain_analyze ?
>> It sounds like bad news that EXPLAIN DELETE might or might not remove rows
>> depending on the state of a variable.
> I have had sessions where not using ANALYZE was the exception, not the
> rule. I would much prefer to type EXPLAIN (ANALYZE OFF) in those cases.

I concur with Justin that having EXPLAIN DELETE/UPDATE actually executing
the query can be too much a bit of a surprise for a user attempting it.

A typical scenario would be "this DELETE/UPDATE query is too slow", admin
connects to production and try safe EXPLAIN on some random sample, and get
bitten because the default was changed.

A way out could be having 3 states for analyse (off, read-only, on) which
would block updates/deletes by making the transaction/operation read-only
to prevent side effects, unless explicitely asked for? I'm not sure if
this can be easily implemented, though. Or maybe run the query in a
separate transaction which is then coldly rollbacked? Hmmm, I'm not really
convincing myself on this one… The safe option seems not allowing to
change ANALYZE option default.

While testing the issue, I'm surprised at the syntax:

EXPLAIN [ ( option [, ...] ) ] statement

Why not allowing the following:

EXPLAIN [ ANALYZE ] [ VERBOSE ] [ ( option [, ...] ) ] statement


In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Fabien COELHO 2020-05-24 08:57:49 Re: Default gucs for EXPLAIN
Previous Message Alvaro Herrera 2020-05-24 02:31:55 Re: Adding missing object access hook invocations