Quick estimate of num of rows & table size

From: Thalis Kalfigkopoulos <tkalfigo(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Quick estimate of num of rows & table size
Date: 2012-11-05 22:02:46
Message-ID: CAEkCx9F6mu7gmxAw_8=sA4HQRUmYruRkN77wRxtd7tvhEuTL3A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I read somewhere that the following query gives a quick estimate of the #
of rows in a table regardless of the table's size (which would matter in a
simple SELECT count(*)?):

SELECT (CASE WHEN reltuples > 0 THEN
pg_relation_size('mytable')/(8192*relpages/reltuples)
ELSE 0
END)::bigint AS estimated_row_count
FROM pg_class
WHERE oid = 'mytable'::regclass;

If relpages & reltuples are recorded accurately each time VACUUM is run,
wouldn't it be the same to just grab directly the value of reltuples like:

SELECT reltuples FROM pg_class WHERE oid='mytable'::regclass;

In the same manner, are pg_relation_size('mytable') and 8192*relpages the
same?

I run both assumptions against a freshly VACUUMed table and they seem
correct.

TIA,
Thalis K.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lonni J Friedman 2012-11-05 22:14:07 Re: Quick estimate of num of rows & table size
Previous Message David Johnston 2012-11-05 21:36:58 Re: Difference between varchar and text?