Query runs on 9.2, but not on 9.4

From: John Scalia <jayknowsunix(at)gmail(dot)com>
To: "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org>
Subject: Query runs on 9.2, but not on 9.4
Date: 2015-08-05 13:56:02
Message-ID: CABzCKRByML4pr5cW373d0vNm-rN-hrt+eda97i2fnEA75qi5HQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Scott Ribe 2015-08-05 14:16:40 Re: Query runs on 9.2, but not on 9.4
Previous Message John Scalia 2015-08-05 01:44:29 Re: pg_basebackup: wal streaming can only be used in plain mode