Date: Wed, 12 Feb 2025 10:22:53 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Sami Imseih <samimseih@gmail.com>, Lukas Fittl <lukas@fittl.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Marko M <marko@pganalyze.com>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>
Subject: Re: [PATCH] Optionally record Plan IDs to track plan changes for a
 query
Message-ID: <Z6wF_eCtCjav3hrQ@jrouhaud>
References: <CAA5RZ0vM9AsEqvKued2drKZJ1opt3wbYaDbxGzi-khkNzwn7og@mail.gmail.com>
 <Z6Ks3ta5089DXiPc@paquier.xyz>
 <CAA5RZ0sfRbd1xcq_oHNA0TPr57yM8qkg-GoD6A0nUfyxZhs33Q@mail.gmail.com>
 <CAP53PkxocbNr+eRag3FEJp3-7S1U80FspOg8UQjO902TWMG=6A@mail.gmail.com>
 <CAA5RZ0u6yJdFL=p5vdpbZFS-2YY+Z6vtzmt4gejgZa3RcNiWMQ@mail.gmail.com>
 <Z6lVcWdNQtgz9BGI@paquier.xyz>
 <CAA5RZ0t80hP2aTv97QtEJy39GkxKmDBVDiTBApfiuTa4O=TEWQ@mail.gmail.com>
 <Z6vmYBgLRLXkx5fi@paquier.xyz>
 <Z6v3dSJ7yEJ9xai4@jrouhaud>
 <Z6wAaGieBgd0VX_8@paquier.xyz>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <Z6wAaGieBgd0VX_8@paquier.xyz>
Archived-At: <https://www.postgresql.org/message-id/Z6wF_eCtCjav3hrQ%40jrouhaud>
Precedence: bulk

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.