CODEWITHSUNDEEP
postgresqlruby-on-rails-4ruby-2.0
Mohd Anas
Mohd Anas

Reputation: 644

Get the Size of Multiple Tables in One Query POSTGRES?

Following on from this prior question about relation sizes:

This query:

query = "CREATE TEMPORARY TABLE query_out AS SELECT * FROM users WHERE is_admin = false"
ActiveRecord::Base.connection.execute(query)

will generate a temporary table and insert all the records from this query i.e 

SELECT * FROM users WHERE is_admin = false

then

ActiveRecord::Base.connection.execute("SELECT pg_size_pretty(pg_relation_size('query_out'))")

I am only getting the the size of one table.

What doI need to do so that I can the size of multiple tables in one single query?

Any help would be appreciated.

Thanks

It will give the size of that temporary table.

Upvotes: 2

Views: 2978

Answers (2)

emre avci
emre avci

Reputation: 11

Following query will returns sum of two tables with union,split_part and sum functions.

select sum(sizeByte) from (select split_part(pg_size_pretty(hypertable_size('table1')), ' ', 1)::bigint *
case split_part(pg_size_pretty(hypertable_size('table1')), ' ', 2)
  when 'bytes' then 1
  when 'kB' then 1024
  when 'MB' then 1024*1024
  when 'GB' then 1024*1024*1024
  when 'TB' then 1024*1024*1024*1024::bigint
end as sizeByte
union
select split_part(pg_size_pretty(hypertable_size('table2')), ' ', 1)::bigint *
case split_part(pg_size_pretty(hypertable_size('table2')), ' ', 2)
  when 'bytes' then 1
  when 'kB' then 1024
  when 'MB' then 1024*1024
  when 'GB' then 1024*1024*1024
  when 'TB' then 1024*1024*1024*1024::bigint
end as sizeByte) t;

Upvotes: 0

Vivek S.
Vivek S.

Reputation: 21895

Following select query will returns all the table and its size's 

SELECT
   relname as mytable,
   pg_size_pretty(pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

Create a VIEW with this select

CREATE VIEW vTableAndSize AS 
SELECT
   relname as mytable,
   pg_size_pretty(pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

and now you can query on this view to get the size like this

SELECT mytable,size 
       FROM vTableAndSize WHERE mytable in ('table1','table2')

As per OP's Comment

CREATE VIEW vTableAndSize_1 as 
SELECT
   relname as mytable,
   (pg_relation_size(relid)) As size
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

and get the size sum of multiple columns using

/* Get sum of specific tables */
SELECT pg_size_pretty(sum(size)) tablesizesum 
       FROM vTableAndSize_1 WHERE mytable in ('table1','table2')

/* Get sum of all tables */
SELECT pg_size_pretty(sum(size)) tablesizesum 
       FROM vTableAndSize_1

Create vTableAndSize_1 in your PostgreSQL database and query like below in your front end(am not familiar with Ruby)

ActiveRecord::Base.connection.execute("SELECT pg_size_pretty(sum(size)) FROM vTableAndSize_1 
WHERE mytable in ('table1','table2')")

Upvotes: 3

Related Questions