pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: wambacher@posteo.de
To: Phil Frost <phil@postmates.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: finding queries doing sequential search
Date: Fri, 14 Dec 2018 19:02:15 +0100
Message-ID: <76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de> (raw)
In-Reply-To: <CAC6ry0+n79ccvS9uyy5O8L6YFx5-7mkZTONhX3N9jQwgZE9BrA@mail.gmail.com>
References: <6dd2fe53-a93d-a2ca-05aa-325921333dac@posteo.de>
<CAC6ry0+n79ccvS9uyy5O8L6YFx5-7mkZTONhX3N9jQwgZE9BrA@mail.gmail.com>
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-boun...;
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;
view thread (5+ messages) latest in thread
Message-ID: <76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de> Permalink: ../76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de/ Also on: postgresql.org/message-id/76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de ·
reply
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Reply to all the recipients using the --to and --cc options: reply via email To: pgsql-admin@postgresql.org Cc: wambacher@posteo.de, phil@postmates.com, pgsql-admin@lists.postgresql.org Subject: Re: finding queries doing sequential search In-Reply-To: <76b3b179-c19d-49c6-d0b9-72d63f3fa375@posteo.de> * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox