pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery runs on 9.2, but not on 9.4
5+ messages / 4 participants
[nested] [flat]
* Query runs on 9.2, but not on 9.4
@ 2015-08-05 13:56 John Scalia <jayknowsunix@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: John Scalia @ 2015-08-05 13:56 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query runs on 9.2, but not on 9.4
@ 2015-08-05 14:16 Scott Ribe <scott_ribe@elevated-dev.com>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Scott Ribe @ 2015-08-05 14:16 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
On Aug 5, 2015, at 7:56 AM, John Scalia <jayknowsunix@gmail.com> wrote:
>
> 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;
So, the first thing I would do is change:
> round(100.0 * count(*) /
> (SELECT setting FROM pg_settings
> WHERE name='shared_buffers')::integer,1)
to:
> (SELECT setting FROM pg_settings
> WHERE name='shared_buffers')::integer,1)
and change:
> round(100.0 * count(*) * 8192 /
> pg_relation_size(c.oid),1)
to:
> pg_relation_size(c.oid),1)
Then look for 0s in those output columns. I'll bet that the 9.4 vs 9.2 difference is simply a value of 0 for one of those, not some exotic thing about the query plan. If I'm right about that, then you can start hunting down a specific explanation.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
https://www.linkedin.com/in/scottribe/
(303) 722-0567 voice
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query runs on 9.2, but not on 9.4
@ 2015-08-05 14:16 Vasilis Ventirozos <v.ventirozos@gmail.com>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Vasilis Ventirozos @ 2015-08-05 14:16 UTC (permalink / raw)
To: pgsql-admin
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@gmail.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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query runs on 9.2, but not on 9.4
@ 2015-08-05 14:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: John Scalia <jayknowsunix@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2015-08-05 14:17 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin
John Scalia <jayknowsunix@gmail.com> writes:
> 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.
It seems likely that you have at least one table in the 9.4 system for
which pg_relation_size() produces 0, but on the 9.2 system that table is
not there or is not physically zero-length.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Query runs on 9.2, but not on 9.4
@ 2015-08-05 14:18 John Scalia <jayknowsunix@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 0 replies; 5+ messages in thread
From: John Scalia @ 2015-08-05 14:18 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin
Thanks guys!! That definitely solved the issue!
On Wed, Aug 5, 2015 at 9:16 AM, Scott Ribe <scott_ribe@elevated-dev.com>
wrote:
> On Aug 5, 2015, at 7:56 AM, John Scalia <jayknowsunix@gmail.com> wrote:
> >
> > 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;
>
> So, the first thing I would do is change:
>
> > round(100.0 * count(*) /
> > (SELECT setting FROM pg_settings
> > WHERE name='shared_buffers')::integer,1)
>
> to:
>
> > (SELECT setting FROM pg_settings
> > WHERE name='shared_buffers')::integer,1)
>
> and change:
>
> > round(100.0 * count(*) * 8192 /
> > pg_relation_size(c.oid),1)
>
> to:
>
> > pg_relation_size(c.oid),1)
>
> Then look for 0s in those output columns. I'll bet that the 9.4 vs 9.2
> difference is simply a value of 0 for one of those, not some exotic thing
> about the query plan. If I'm right about that, then you can start hunting
> down a specific explanation.
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> https://www.linkedin.com/in/scottribe/
> (303) 722-0567 voice
>
>
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-08-05 14:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-08-05 13:56 Query runs on 9.2, but not on 9.4 John Scalia <jayknowsunix@gmail.com>
2015-08-05 14:16 ` Scott Ribe <scott_ribe@elevated-dev.com>
2015-08-05 14:18 ` John Scalia <jayknowsunix@gmail.com>
2015-08-05 14:16 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
2015-08-05 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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