How can I calculate the size of a table using a condition

Question

How can I count the table size in MySQL or PosgreSQL using a restriction? for example: WHERE organisation_id=1

Answer 1

If you have associations set up in your models as follows:

class Organisation < ActiveRecord::Base
  has_many :members
end

class Member < ActiveRecord::Base
  belongs_to :organisation
end

then you can find how many members are in organisation 1 with the following:

organisation = Organisation.find(1)
n_members = organisation.members.size

Thanks to ActiveRecord, this kind of thing is database independent.

Answer 2

In PostgreSQL you can't. Data is stored in blocks (normaly 8kb each) and you have no idea in what block the records with "organisation_id=1" are stored. When all records are in one block, it's 8kb. When 100 records are stored in 100 blocks, it's 800kb, but that includes other records as well.

You could do some magic with the hidden column "ctid", but that's a lot of work, not accurate and not very usefull as well. Just use pg_relation_size() to get the entire table, that's what you need. If you need the TOAST-table includes, use pg_total_relation_size().

Answer 3

ModelName.count(:conditions => {:organisation_id => 1})

Answer 4

Even shorter version

ModelName.count(:organisation_id => 1)

Answer 5

It depends on your table structure. If you have all fixed width fields you can get pretty close. If you have variable width fields the best you will do is an estimate. This is for postgres.

SELECT  pg_total_relation_size('my_table') * i / cnt
FROM (
  SELECT SUM(CASE WHEN organization_id = 1 THEN 1 ELSE 0 END) AS i,
  COUNT(1) AS cnt
  FROM my_table
) sub