From: "Pavlo Golub" <pavlo.golub@cybertec.at>
To: "Christoph Berg" <myon@debian.org>
Subject: Re[2]: [PATCH] Add last_executed timestamp to pg_stat_statements
Cc: "Sami Imseih" <samimseih@gmail.com>, "Bertrand Drouvot"
 <bertranddrouvot.pg@gmail.com>, "pgsql-hackers@lists.postgresql.org"
 <pgsql-hackers@lists.postgresql.org>
Date: Fri, 06 Feb 2026 11:19:26 +0000
Message-Id: <ema0100e49-522e-4b8f-9c25-9257af1f0793@cybertec.at>
In-Reply-To: <aYXH4hkFc_2xOa8t@msg.df7cb.de>
References: <CAK7ymc+FxoVswo1ok_xDW-xPG-ZEZ8SAqCUkJ7WF04=0aQDvVQ@mail.gmail.com>
 <CAA5RZ0uyvnNuHRj=73jZhocnLxE07oM-AeSLS=FUuuZ_hy7fKA@mail.gmail.com>
 <em5be966c3-9086-4f3f-b516-97444b854fdf@10e48d49.com>
 <CAA5RZ0u6qdcLoWJJz5mUB_VYZqT__U-HJJ5_ioYO7XGsUfXfpw@mail.gmail.com>
 <aYIh7I9khvoewwH_@msg.df7cb.de>
 <CAA5RZ0tH_+3JGMCktkp8EMyvKaRAJf-xRh+y_UoZ8azvuUEAYQ@mail.gmail.com>
 <aYMKolTlvzenr4Pk@ip-10-97-1-34.eu-west-3.compute.internal>
 <CAK7ymc+E00zKC9Da+BK2se=O2RiOUkdW5dz4iAynD1Vj=Pwexg@mail.gmail.com>
 <CAA5RZ0u_jjiLc_w4nksdVazvvqtE17C1nM=sKf9fy5x02orfsg@mail.gmail.com>
 <emed32d635-e46b-4f08-9085-3fd822fe5765@cybertec.at>
 <aYXH4hkFc_2xOa8t@msg.df7cb.de>
Reply-To: "Pavlo Golub" <pavlo.golub@cybertec.at>
User-Agent: eM_Client/10.4.4209.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/ema0100e49-522e-4b8f-9c25-9257af1f0793%40cybertec.at>
Precedence: bulk

>
>I still believe that storing execution start time is the wrong thing
>to do as it will miss all long-running statements. Consider this
>timeline:
>
>09:55 get all stats changed since 09:50
>       -> doesn't see the statement because it hasn't started yet
>09:57 start long-running statement
>10:00 get all stats changed since 09:55
>       -> doesn't see the statement because it's still running
>10:02 long-running statement terminates, storing 09:57 as timestamp
>10:05 get all stats changed since 10:00
>       -> doesn't see the statement because it's too old
>

Thanks for sharing this. I spent a sleepless night and it seems I found=20
the solution for the issue.

If we do

   WHERE last_execution_start + max_exec_time * INTERVAL '1 ms' > NOW() -=
=20
polling_interval

we will grab all long-running statements. The worst thing that might=20
happen, if some query
has significant run time deviation, then we could grab it several times.=20
But again this is far
better than re-fetching thousands of rows every time.

For short queries it will just work as expected. "Issues" might be only=20
with long queries with
a high deviation longer than polling interval. But fetching one or two=20
rows once in a while is not
a problem.