Re: Query runs on 9.2, but not on 9.4

Hey John,
this happens because pg_relation_size(c.oid) returns 0 for one or more
rows, try :

SELECT
c.relname,pg_size_pretty(count(*) * 8192) as buffered,round(100.0 *
count(*) / (SELECT setting FROM pg_settings WHERE
name='shared_buffers')::integer,1) AS buffers_percent,
round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS
percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b
ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d
ON (b.reldatabase = d.oid AND d.datname = current_database())
where pg_relation_size(c.oid) >0
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 25;
i'll also put this on my forum, thanks for reading btw :)

On Wed, Aug 5, 2015 at 4:56 PM, John Scalia <jayknowsunix(at)gmail(dot)com> wrote:

> Hi all,
>
> I've got a query I use to show the amount of shared buffers in use that is
> giving me some issues. It uses the pg_buffercache extension. The query is:
>
> SELECT
> c.relname,
> pg_size_pretty(count(*) * 8192) as buffered,
> round(100.0 * count(*) /
> (SELECT setting FROM pg_settings
> WHERE name='shared_buffers')::integer,1)
> AS buffers_percent,
> round(100.0 * count(*) * 8192 /
> pg_relation_size(c.oid),1)
> AS percent_of_relation
> FROM pg_class c
> INNER JOIN pg_buffercache b
> ON b.relfilenode = c.relfilenode
> INNER JOIN pg_database d
> ON (b.reldatabase = d.oid AND d.datname = current_database())
> GROUP BY c.oid,c.relname
> ORDER BY 3 DESC
> LIMIT 25;
>
> On a 9.2 system, it runs fine and produces a list of the top 25 tables
> using the shared buffer, but on 9.3 and above, it throw a "division by
> zero" error. I've run explain for the query on both the 9.2 and the 9.4
> server, and the only difference I can see is the 9.4 server shows an
> additional index scan that the 9.2 server does not. Here's the explain from
> the 9.4 server:
>
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------------------
> Limit (cost=59.73..59.74 rows=5 width=68)
> InitPlan 1 (returns $0)
> -> Function Scan on pg_show_all_settings a (cost=0.00..12.50 rows=5
> width=32)
> Filter: (name = 'shared_buffers'::text)
> -> Sort (cost=47.22..47.24 rows=5 width=68)
> Sort Key: (round(((100.0 * (count(*))::numeric) /
> (($0)::integer)::numeric), 1))
> -> HashAggregate (cost=46.91..47.16 rows=5 width=68)
> Group Key: c.oid, c.relname
> -> Nested Loop (cost=1.30..46.85 rows=5 width=68)
> -> Hash Join (cost=1.03..14.83 rows=5 width=4)
> Hash Cond: (p.reldatabase = d.oid)
> -> Function Scan on pg_buffercache_pages p
> (cost=0.00..10.00 rows=1000 width=8)
> -> Hash (cost=1.01..1.01 rows=1 width=4)
> -> Seq Scan on pg_database d
> (cost=0.00..1.01 rows=1 width=4)
> Filter: (datname =
> current_database())
> -> Index Scan using
> pg_class_tblspc_relfilenode_index on pg_class c (cost=0.28..6.39 rows=1
> width=72)
> Index Cond: (relfilenode = p.relfilenode)
> (17 rows)
>
> The explain for the 9.2 server is the same, except as noted, the 9.4 has
> that additional Index Scan at the very end. I've tried contacting the
> original author on evol-monkey.blogspot.com, but the authentication there
> is broken, and won't let me comment on his page.
>
> Anybody have any ideas for why this breaks and what I can do to fix it?
> I've been looking at for a day or so with no success.
> --
> Jay
>