public inbox for [email protected]
help / color / mirror / Atom feedFrom: Julien Rouhaud <[email protected]>
To: Michael Paquier <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: Lukas Fittl <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Marko M <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Subject: Re: [PATCH] Optionally record Plan IDs to track plan changes for a query
Date: Wed, 12 Feb 2025 10:22:53 +0800
Message-ID: <Z6wF_eCtCjav3hrQ@jrouhaud> (raw)
In-Reply-To: <[email protected]>
References: <CAA5RZ0vM9AsEqvKued2drKZJ1opt3wbYaDbxGzi-khkNzwn7og@mail.gmail.com>
<[email protected]>
<CAA5RZ0sfRbd1xcq_oHNA0TPr57yM8qkg-GoD6A0nUfyxZhs33Q@mail.gmail.com>
<CAP53PkxocbNr+eRag3FEJp3-7S1U80FspOg8UQjO902TWMG=6A@mail.gmail.com>
<CAA5RZ0u6yJdFL=p5vdpbZFS-2YY+Z6vtzmt4gejgZa3RcNiWMQ@mail.gmail.com>
<[email protected]>
<CAA5RZ0t80hP2aTv97QtEJy39GkxKmDBVDiTBApfiuTa4O=TEWQ@mail.gmail.com>
<[email protected]>
<Z6v3dSJ7yEJ9xai4@jrouhaud>
<[email protected]>
On Wed, Feb 12, 2025 at 10:59:04AM +0900, Michael Paquier wrote:
> On Wed, Feb 12, 2025 at 09:20:53AM +0800, Julien Rouhaud wrote:
> >
> > FTR my main motivation was to be able to deal with queries referencing
> > temporary relations, as if your application creates a lot of those it basically
> > means that you cannot use pg_stat_statements anymore.
>
> Do you have an issue more details about your problem? If we can
> improve the situation in core without impacting the existing cases
> that we need to support in pgss, that may be worth looking at.
I thought this was a well known limitation. The basic is that if you rely on
temp tables, you usually end up with a virtually infinite number of queryids
since all temp tables get a different oid and that oid is used in the queryid
computation. And in that case the overhead of pg_stat_statements is insanely
high. The last figures I saw was by Andres many years ago, with a mention 40%
overhead, and I don't think it's hard to get way worse overhead than that if
you have lengthier query texts.
As a prototype in my extension I think I just entirely ignored such queries,
but another (and probably friendlier for the actual pg_stat_statements
statistics) approach would be to use the relation name to compute the queryid
rather than its oid. This would add some overhead, but I think it would have
very limited impact especially compared to the current situation.
Of course some people may want to keep the current behavior, if they have
limited number of temp tables or similar, so I had a GUC for that. I don't
think that the community would really welcome such GUC for core-postgres,
especially since it wouldn't be pg_stat_statements specific.
view thread (34+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Optionally record Plan IDs to track plan changes for a query
In-Reply-To: <Z6wF_eCtCjav3hrQ@jrouhaud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox