ne 18. 4. 2021 v 14:28 odesÃlatel Julien Rouhaud <rjuju...@gmail.com> napsal:
> On Sun, Apr 18, 2021 at 06:21:56AM +0200, Pavel Stehule wrote: > > > > The extension like pg_qualstat is good, but it does different work. > > Yes definitely. It was just an idea if you needed something right now that > could more or less do what you needed, not saying that we shouldn't > improve the > core :) > > > In > > complex applications I need to detect buggy (forgotten) queries - last > week > > I found two queries over bigger tables without predicates. So the > qualstat > > doesn't help me. > > Also not totally helpful but powa was created to detect problematic > queries in > such cases. It wouldn't say if it's because of a seq scan or not (so yes > again > we need to improve that), but it would give you the slowest (or top > consumer > for any resource) for a given time interval. > > > This is an application for a government with few (but for > > government typical) specific: 1) the life cycle is short (one month), 2) > > there is not slow start - from first moment the application will be used > by > > more hundred thousands people, 3) the application is very public - so any > > issues are very interesting for press and very unpleasant for politics, > and > > in next step for all suppliers (there are high penalty for failures), and > > an admins are not happy from external extensions, 4) the budget is not > too > > big - there is not any performance testing environment > > > > First stages are covered well today. We can log and process very slow > > queries, and fix it immediately - with CREATE INDEX CONCURRENTLY I can do > > it well on production servers too without high risk. > > > > But the detection of some bad not too slow queries is hard. And as an > > external consultant I am not able to install any external extensions to > the > > production environment for fixing some hot issues, The risk is not > > acceptable for project managers and I understand. So I have to use only > > tools available in Postgres. > > Yes I agree that having additional and more specialized tool in core > postgres > would definitely help in similar scenario. > > I think that having some kind of threshold for seq scan (like the mentioned > auto_explain.log_seqscan = XXX) in auto_explain would be the best > approach, as > you really need the plan to know why a seq scan was chosen and if it was a > reasonable choice or not. > I would like to write this for core and for auto_explain too. I was in a situation when I hadnot used auto_explain too. Although this extension is widely used and then the risk is low. When I detect the query, then I can run the explanation manually. But sure I think so it can work well inside auto_explain Regards Pavel
