Closed banlex73 closed 3 years ago
Unfortunately, everything in powa is based on pg_stat_statements, including most of other extensions. If you have a now performance query that is barely run and a lot of other short queries, then yes it's quite likely that pg_stat_statements will quickly evict it, making it invisible to powa.
Have you tried to check how often are queries evicted from pg_stat_statements (in currently released version, the only way to do that is to do a count(*) from pg_stat_statements and see when it's going down, next version of postgres will include more stats about eviction). If you can avoid eviction without raising pg_stat_statements.max that could be a good thing, as unfortunately eviction is quite costly and can bring a lot of overhead.
You should also avoid doing pg_stat_statements.reset(). This will also add overhead, but it can also bring some unexpected problem with powa, as powa doesn't handle queries that are not executed across multiple snapshot (see https://powa.readthedocs.io/en/latest/FAQ.html#some-queries-don-t-show-up-in-the-ui). In your case, if you can't find the query in pg_stat_statements (or powa_statements), then that's not your issue though.
much appreciate for your detailed explanation and useful link!
It's rather question then issue, I have ad-hoc query what is not performing well, but cannot find anything about it in powa because my query is not in pg_stat_statements. Why it's not there? because cluster is pretty busy and if query runs only once it will never be there.. What I tried: