Re: proposal - log_full_scan

ne 18. 4. 2021 v 14:28 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)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