Date: Fri, 9 Aug 2019 18:27:04 -0700 (MST)
From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Message-ID: <1565400424579-0.post@n3.nabble.com>
In-Reply-To: 
 <CAOBaU_ZgCPB-N261Gh2+35vakyKHFHcEbCFrCrwST_UEyUjr4w@mail.gmail.com>
References: <1553029215728-0.post@n3.nabble.com>
 <0A3221C70F24FB45833433255569204D1FBE20A4@G01JPEXMBYT05>
 <1553110765067-0.post@n3.nabble.com>
 <CAOBaU_ZJShkK40_suw9NtUZEtoM07PYg4c_G2m_=HyXQ+pzoAw@mail.gmail.com>
 <1553116679891-0.post@n3.nabble.com> <1553117433667-0.post@n3.nabble.com>
 <CAOBaU_atk6WMxA=ajNURThwmJ2jMyEfkE88TSkwtOfvk4gXrFA@mail.gmail.com>
 <1553120398016-0.post@n3.nabble.com>
 <20190409212613.udbyn2ezioz2clwn@momjian.us>
 <CAOBaU_ZgCPB-N261Gh2+35vakyKHFHcEbCFrCrwST_UEyUjr4w@mail.gmail.com>
Subject: Re: [survey] New "Stable" QueryId based on normalized query text
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk

I missed this thread.  I'd be happy to post the code for what we use as the
stable query identifier, but we could definitely come up with a more
efficient algorithm if we're willing to assume that the sql statements are
the same if and only if the parse tree structure is the same.

Currently what we do for the sql hash is to simply replace all the literals
and then hash the resulting SQL string, because for our use case we wanted
to be insensitive to the even the structure of the parse tree from one
release to the next.  That may be too conservative for other use cases.  If
it's ok to assume that the structure of the Query tree doesn't change, then
you could define a stable identifier for each node type, ignore literal
constants, and hash fully-qualified object names instead of OIDs.  That
should be pretty fast.

We also compute a plan hash that converts Plan tree node id's into stable
identifiers, and computes a cheap hash function over all nodes in the plan. 
This is fast and efficient.  It's also pretty straightforward to convert
node id's to stable identifiers.

A complication that we recently had to deal with was hashing and normalizing
the text of queries inside pl/pgsql functions, where variables are converted
to parameter markers.  In that case the sql text is transformed to contain
both parameter markers and literal replacement markers before computing the
sql hash.


-----
Jim Finnerty, AWS, Amazon Aurora PostgreSQL
--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html