From: | wambacher(at)posteo(dot)de |
---|---|
To: | Phil Frost <phil(at)postmates(dot)com> |
Cc: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: finding queries doing sequential search |
Date: | 2018-12-14 18:02:15 |
Message-ID: | 76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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 <https://wambachers-osm.website/boundaries>
Missing Boundaries
<https://wambachers-osm.website/index.php/projekte/internationale-administrative-grenzen/missing-boundaries>
Emergency Map <https://wambachers-osm.website/emergency>
Postal Code Map (Germany only) <https://wambachers-osm.website/plz>
Fools (QA for zipcodes in Germany) <https://wambachers-osm.website/fools>
Postcode Boundaries of Germany <https://wambachers-osm.website/pcoundaries>
From | Date | Subject | |
---|---|---|---|
Next Message | Alessandro Manzoni | 2018-12-14 18:40:54 | Where is util.py |
Previous Message | Phil Frost | 2018-12-14 17:39:13 | Re: finding queries doing sequential search |