pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding queries doing sequential search
5+ messages / 4 participants
[nested] [flat]
* finding queries doing sequential search
@ 2018-12-14 15:39 wambacher@posteo.de
0 siblings, 3 replies; 5+ messages in thread
From: wambacher@posteo.de @ 2018-12-14 15:39 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
Regards
walter
--
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;
Attachments:
seq_scans.png (image/png, 50.7K) download
view image
* Re: finding queries doing sequential search
@ 2018-12-14 15:59 Tom Lane <tgl@sss.pgh.pa.us>
parent: wambacher@posteo.de
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2018-12-14 15:59 UTC (permalink / raw)
To: wambacher@posteo.de; +Cc: pgsql-admin@lists.postgresql.org
wambacher@posteo.de writes:
> 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.
auto_explain might help. Or you could just log_min_duration_statement
to capture queries that are running long.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: finding queries doing sequential search
@ 2018-12-14 17:39 Phil Frost <phil@postmates.com>
parent: wambacher@posteo.de
2 siblings, 1 reply; 5+ messages in thread
From: Phil Frost @ 2018-12-14 17:39 UTC (permalink / raw)
To: wambacher@posteo.de; +Cc: pgsql-admin@lists.postgresql.org
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@posteo.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?
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: finding queries doing sequential search
@ 2018-12-14 18:02 wambacher@posteo.de
parent: Phil Frost <phil@postmates.com>
0 siblings, 0 replies; 5+ messages in thread
From: wambacher@posteo.de @ 2018-12-14 18:02 UTC (permalink / raw)
To: Phil Frost <phil@postmates.com>; +Cc: pgsql-admin@lists.postgresql.org
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;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: finding queries doing sequential search
@ 2018-12-15 13:55 Laurenz Albe <laurenz.albe@cybertec.at>
parent: wambacher@posteo.de
2 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2018-12-15 13:55 UTC (permalink / raw)
To: wambacher@posteo.de; pgsql-admin@lists.postgresql.org
wambacher@posteo.de wrote:
> 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?
With a big table it shouldn't be a problem.
Set log_min_duration_statement = 10000 or similar, and all long running
statements will be logged. The sequential scans should be among them.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2018-12-15 13:55 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-12-14 15:39 finding queries doing sequential search wambacher@posteo.de
2018-12-14 15:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-12-14 17:39 ` Phil Frost <phil@postmates.com>
2018-12-14 18:02 ` wambacher@posteo.de
2018-12-15 13:55 ` Laurenz Albe <laurenz.albe@cybertec.at>
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