Re: proposal - log_full_scan

so 17. 4. 2021 v 18:54 odesílatel Julien Rouhaud <rjuju123(at)gmail(dot)com>
napsal:

> On Sat, Apr 17, 2021 at 05:22:59PM +0200, Pavel Stehule wrote:
> >
> > The fullscan of this table needs about 30ms and has 200K rows. So
> > decreasing log_min_duration to this value is very risky.
> >
> > [...]
> >
> > I use pg_stat_all_tables.seq_scan and I see seq scans there. But I need
> to
> > know the related queries.
>
> Maybe you could use pg_qualstats ([1]) for that? It will give you the
> list of
> quals (with the underlying queryid) with a tag to specify if they were
> executed
> as an index scan or a sequential scan. It wouldn't detect queries doing
> sequential scan that don't have any qual for the underlying relations, but
> those shouldn't be a concern in your use case.
>
> If you setup some sampling, the overhead should be minimal.
>
> [1]: https://github.com/powa-team/pg_qualstats/

It has similar functionality - there is a problem with setting. The my idea
is more simple - just

set

log_fullscall_min_tupples = 100000

or

alter table xxx set log_fullscan_min_tupples = 0;

and then the complete query can be found in the log.

I think this can be really practical so it can be core functionality. And
it can log the queries without
quals too. The productions systems can be buggy and it is important to find
bugs

Regards

Pavel