Re: proposal - log_full_scan - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: proposal - log_full_scan
Date
Msg-id CAFj8pRBnWdkZLv6EvxtaRRdeU5WmCibr2KvHYnSY__xRCiwfzQ@mail.gmail.com
Whole thread Raw
In response to Re: proposal - log_full_scan  (Pavel Stehule <pavel.stehule@gmail.com>)
Responses Re: proposal - log_full_scan
List pgsql-hackers


ne 18. 4. 2021 v 16:09 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:


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

I tried to write the patch. It is harder work for core, than I expected, because there is not any good information if the query is top or not, so it is not easy to decide if we should log info or not.

On second hand, the modification of auto_explain is easy.

I am sending the patch

Regards

Pavel


Regards

Pavel

Attachment

pgsql-hackers by date:

Previous
From: Andrew Dunstan
Date:
Subject: Re: pg_amcheck option to install extension
Next
From: Peter Geoghegan
Date:
Subject: Re: Bogus collation version recording in recordMultipleDependencies