search

zilder / pg_dropcache

pg_dropcache is a PostgreSQL extension that invalidates shared_buffers cache
26 stars 5 forks source link

Feature: support schemas #5

Open DKroot opened 6 years ago

DKroot commented 6 years ago

It'd be nice to be able to limit cache clearing to a particular entity only in a particular schema.

Supporting query that includes schemas:

-- Cache, ordered by the number of buffers
SELECT pn.nspname AS schema, pc.relname, count(1) AS buffers, sum(pb.isdirty :: INT) AS dirty_pages
FROM pg_buffercache pb
JOIN pg_class pc ON pb.relfilenode = pg_relation_filenode(pc.oid) --
    AND pb.reldatabase IN(0,
                          (SELECT oid
                           FROM pg_database
                           WHERE datname = current_database()))
JOIN pg_namespace pn ON pn.oid = pc.relnamespace
GROUP BY pc.relname, pn.nspname
ORDER BY buffers DESC;