From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Paquier <michael@paquier.xyz>
Cc: Jerry Brenner <jbrenner@guidewire.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Does Postgres have consistent identifiers (plan hash value) for explain plans?
Date: Mon, 04 Dec 2023 22:29:55 -0500
Message-ID: <842921.1701746995@sss.pgh.pa.us> (raw)
In-Reply-To: <ZW6TdlN7Im7h2KCd@paquier.xyz>
References: <CACoKFYTVi5eq2Y2v5ni1X9KrDdv8HS_fPdU_rYm6Lr6p=0wjeQ@mail.gmail.com>
<756532.1701701844@sss.pgh.pa.us>
<ZW6TdlN7Im7h2KCd@paquier.xyz>
Michael Paquier <michael@paquier.xyz> writes:
> On Mon, Dec 04, 2023 at 09:57:24AM -0500, Tom Lane wrote:
>> Jerry Brenner <jbrenner@guidewire.com> writes:
>>> Both Oracle and SQL Server have
>>> consistent hash values for query plans and that makes it easy to identify
>>> when there are multiple plans for the same query. Does that concept exist
>>> in later releases of Postgres (and is the value stored in the json explain
>>> plan)?
>> No, there's no support currently for obtaining a hash value that's
>> associated with a plan rather than an input query tree.
> PlannerGlobal includes a no_query_jumble that gets inherited by all
> its lower-level nodes, so adding support for hashes compiled from
> these node structures would not be that complicated. My point is that
> the basic infrastructure is in place in the tree to be able to do
> that, and it should not be a problem to even publish the compiled
> hashes in EXPLAIN outputs, behind an option of course.
Well, yeah, we could fairly easily activate that infrastructure for
plans, but we haven't. More to the point, it's not clear to me that
that would satisfy the OP's request for "consistent" hash values.
The hashes would vary depending on object OID values, system version,
possibly endianness, etc.
I'm also wondering exactly what the OP thinks qualifies as different
plans. Remembering the amount of fooling-around that's gone on with
querytree hashes to satisfy various people's ill-defined desires for
pg_stat_statements aggregation behavior, I'm not really eager to buy
into the same definitional morass at the plan level.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, michael@paquier.xyz, jbrenner@guidewire.com, pgsql-performance@lists.postgresql.org
Subject: Re: Does Postgres have consistent identifiers (plan hash value) for explain plans?
In-Reply-To: <842921.1701746995@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox