finding queries doing sequential search

From: wambacher(at)posteo(dot)de
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: finding queries doing sequential search
Date: 2018-12-14 15:39:30
Message-ID: 6dd2fe53-a93d-a2ca-05aa-325921333dac@posteo.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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-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>

Attachment Content-Type Size
image/png 50.7 KB

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2018-12-14 15:59:53 Re: finding queries doing sequential search
Previous Message Laurenz Albe 2018-12-14 14:33:32 Re: pg_attribute getting bloated in size