Re: proposal: simple query profile and tracing API

On Tue, 2018-11-13 at 13:55 +0100, Pavel Stehule wrote:
> út 13. 11. 2018 v 13:12 odesílatel legrand legrand <
> legrand_legrand@hotmail.com> napsal:
>
> > Hello Pavel,
> >
> > What about using wait events and a trigger on pg_stat_activity ?
> >
>
> pg_stat_activity should not to show fresh data. Using
> pg_stat_activity can be too expensive for fast queries
>

More importantly, how would you create a trigger on pg_stat_activity,
considering it's a system view backed by SRF?

> > ...
> > An other solution: a customized version of pgsentinel (for high
> > frequency sampling):
> >
>
> I don't believe to sampling method - I talk about less than 10ms
> queries, I would to see a 2-3ms planning time, 2-5ms waitings - and
> it means sampling aboy 2ms, what is expensive
>

You're quietly assuming that whatever alternative solution you end up
inventing will be cheaper than this sampling. Which is going to be
hard, if you want to do that for every execution of even the shortest
queries. I'd say that's doomed to fail.

Moreover, the sampling does not need to catch every query execution.
The idea is to do it "just often enough" for some desired accuracy. For
example you might pick 10ms interval - it will hit even shorter queries
if they are executed often enough (and if they're not, who cares about
them?). And given the sample percentages and total time, you can do
some estimates for each query / phase.

regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services