Re[2]: [PATCH] Add last_executed timestamp to pg_stat_statements

>
>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
the solution for the issue.

If we do

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

we will grab all long-running statements. The worst thing that might
happen, if some query
has significant run time deviation, then we could grab it several times.
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
with long queries with
a high deviation longer than polling interval. But fetching one or two
rows once in a while is not
a problem.