Subject: Re: Multiple Query IDs for a rewritten parse tree
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
References: <e0de3423-4bba-1e69-c55a-f76bf18dbd74@postgrespro.ru>
 <1606733.1641689399@sss.pgh.pa.us> <20220109121321.3d74krhjbr5mp4zv@jrouhaud>
 <1ea9d518-becb-1c3d-dc6c-e0045d3101de@postgrespro.ru>
 <Ydu7a08KPZKz4sUC@jrouhaud>
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
Message-ID: <e825ae62-808b-fa2e-057f-f47d5af1c04e@postgrespro.ru>
Date: Mon, 10 Jan 2022 12:37:34 +0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
 Thunderbird/78.14.0
MIME-Version: 1.0
In-Reply-To: <Ydu7a08KPZKz4sUC@jrouhaud>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/e825ae62-808b-fa2e-057f-f47d5af1c04e%40postgrespro.ru>
Precedence: bulk

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