Re: proposal - log_full_scan

ne 18. 4. 2021 v 14:28 odesílatel Julien Rouhaud <rjuju123@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