Обсуждение: explain (verbose off, normalized) vs query planid

Hello,

as described in:
http://www.postgresql-archive.org/Re-FEATURE-PATCH-pg-stat-statements-with-plans-v02-td6015488.html

I'm wondering about the best way to build a query planid. 

It seems natural (to me) to calculate a hash value based on the normalized
plan text 
generated by

explain (costs off, normalized) 
or maybe
explain (costs off, verbose, normalized)

this normalized output can be done by adding a modified get_const_expr in
ruleutils.c as proposed in attached file.

Would this "explain normalized" feature be interesting for core team and or
users ?

Regards
PAscal

get_const_expr_normalized.c
<http://www.postgresql-archive.org/file/t348768/get_const_expr_normalized.c>  






--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Sat, May 12, 2018 at 6:07 PM, legrand legrand
<legrand_legrand@hotmail.com> wrote:
> It seems natural (to me) to calculate a hash value based on the normalized
> plan text
> generated by
>
> explain (costs off, normalized)
> or maybe
> explain (costs off, verbose, normalized)

I would think it would be preferable to do it based on the node tree,
like pg_stat_statements does for query fingerprinting.  But I guess it
might depend on what you want to do with it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

This is for tracking planid into pg_stat_statements too.

A first try is available here
http://www.postgresql-archive.org/Poc-pg-stat-statements-with-planid-td6014027.html

reusing pg_stat_plans's "Plan tree Jumbling algorithm" from Peter Geoghegan.

Hashing the normalized query plan text in one pass (that is also based on
plan tree), 
compared to that Jumbling method seems simple to maintain (if explain works,
planid is available and accurate). 
Today actual planid jumbling comes from pg9.3, doesn't compile anymore, and
I'm not able to verify 
if declarativ partitionning is properly handled ...  

Would there be some functional or performances reasons to prefer jumbling to
hashing normalized plan text?

Regards
PAscal   



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html

On Tue, May 15, 2018 at 3:45 PM, legrand legrand
<legrand_legrand@hotmail.com> wrote:
> Would there be some functional or performances reasons to prefer jumbling to
> hashing normalized plan text?

Using the text could produce different query IDs for the same plan if
any information is displayed in the text format which can vary for
reasons other than a plan change.  I don't know if there are any, but
what about, for example, the effect of GUCs on how timestamps are
displayed?  Or, much worse, what if a timer value creeps into the
output somehow?  Certainly, renaming a table is going to change the
output.  Even using a different table alias will change the output.

Using the text could produce the same query ID for different plans if
there's any relevant detail of the plan that's not shown in the text.

Basically, I would be nervous about the idea of an EXPLAIN output
that's required to reflect all and only the plan details that should
be jumbled.  The "normalized" option to EXPLAIN which you mentioned
upthread doesn't exist today...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> On Tue, May 15, 2018 at 3:45 PM, legrand legrand
> <legrand_legrand@hotmail.com> wrote:
>> Would there be some functional or performances reasons to prefer jumbling to
>> hashing normalized plan text?

> Basically, I would be nervous about the idea of an EXPLAIN output
> that's required to reflect all and only the plan details that should
> be jumbled.  The "normalized" option to EXPLAIN which you mentioned
> upthread doesn't exist today...

Indeed, and if we did write it, I think it would largely consist of
throwing away info that a jumbling mechanism could ignore far more easily.
Not to mention that we'd have to expend the cycles to emit a text
representation that we didn't actually have use for.  It sounds like a
complete loser both in terms of coding effort and runtime performance.

            regards, tom lane