On 1/10/22 9:51 AM, Julien Rouhaud wrote:
On Mon, Jan 10, 2022 at 09:10:59AM +0500, Andrey V. Lepikhov wrote:
I can add one more use case.
Our extension for freezing query plan uses query tree comparison technique
to prove, that the plan can be applied (and we don't need to execute
planning procedure at all).
The procedure of a tree equality checking is expensive and we use cheaper
queryId comparison to identify possible candidates. So here, for the better
performance and queries coverage, we need to use query tree normalization -
queryId should be stable to some modifications in a query text which do not
change semantics.
As an example, query plan with external parameters can be used to execute
constant query if these constants correspond by place and type to the
parameters. So, queryId calculation technique returns also pointers to all
constants and parameters found during the calculation.
I'm also working on a similar extension, and yes you can't accept any
fingerprinting approach for that. I don't know what are the exact heuristics
of your cheaper queryid calculation are, but is it reasonable to use it with
something like pg_stat_statements? If yes, you don't really need two queryid
approach for the sake of this single extension and therefore don't need to
store multiple jumble state or similar per statement. Especially since
requiring another one would mean a performance drop as soon as you want to use
something as common as pg_stat_statements.
I think, pg_stat_statements can live with an queryId generator of the
sr_plan extension. But It replaces all constants with $XXX parameter at
the query string. In our extension user defines which plan is optimal
and which constants can be used as parameters in the plan.
One drawback I see here - creating or dropping of my extension changes
behavior of pg_stat_statements that leads to distortion of the DB load
profile. Also, we haven't guarantees, that another extension will work
correctly (or in optimal way) with such queryId.
--
regards,
Andrey Lepikhov
Postgres Professional
