pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jim Finnerty <jfinnert@amazon.com>
To: pgsql-hackers@postgresql.org
Subject: Re: [survey] New "Stable" QueryId based on normalized query text
Date: Fri, 9 Aug 2019 18:27:04 -0700 (MST)
Message-ID: <1565400424579-0.post@n3.nabble.com> (raw )
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 >
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
view thread (26+ messages) latest in thread
Message-ID: <1565400424579-0.post@n3.nabble.com>
Permalink: ../1565400424579-0.post@n3.nabble.com/
Also on: postgresql.org/message-id/1565400424579-0.post@n3.nabble.com
copy link · copy postgr.es
reply 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-hackers@postgresql.org
Cc: jfinnert@amazon.com
Subject: Re: [survey] New "Stable" QueryId based on normalized query text
In-Reply-To: <1565400424579-0.post@n3.nabble.com>
* 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