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@xxxxxxxxx> 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?
