Re: finding queries doing sequential search

[wambacher@xxxxxxxxx]

Am 14.12.18 um 18:39 schrieb Phil Frost:

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.

3.6 mio tup is quite much, i think:

select seq_tup_read,seq_scan, seq_tup_read/seq_scan "tup per scan" from pg_stat_user_tables
where relname = 'planet_osm_polygon';
 seq_tup_read | seq_scan | tup per scan
--------------+----------+--------------
   1517870594 |      426 |      3563076
(1 Zeile)

@tom: I'll try auto_explain next.

--
My projects:

Admin Boundaries of the World
Missing Boundaries
Emergency Map
Postal Code Map (Germany only)
Fools (QA for zipcodes in Germany)
Postcode Boundaries of Germany