Re: finding queries doing sequential search

I look for queries with a high shared_blks_hit and/or shared_blks_read in
pg_stat_statements. Although not directly indicative of sequential scans,
the correlation is usually pretty good.

Also I suggest you look in pg_stat_user_tables and ensure that seq_tup_read
is high as well as seq_scan. For example this query:

select * from a_really_big_table limit 1;

will increment seq_scan, though since it scans only one tuple there's not
actually any problem here. Dividing seq_tup_read by seq_scan will yield the
mean number of tuples read per sequential scan, and if that's a small
number it could be that the sequential scans you are seeing are in fact a
non-issue.

On Fri, Dec 14, 2018 at 10:39 AM <wambacher(at)posteo(dot)de> wrote:

> Hi,
>
> i have a very big table (PostGIS OpenStreetMap data, but that does not
> mapper) with about 380 million records and a size of 275 GB + Indices.
> Permanently updated and growing.
>
> Of course i enabled autovacuum.
>
> The log shows me 8.2 Mio index scans (iscan), which is fine, and right
> now 424 sequential scans (sscan) for planet_osm_polygon. see attached image.
>
> I want to locate the query doing this stuff but EXPLAIN won't help because
> most querys are dynamicly created.
>
> Is there a way/trick do this?
>