Обсуждение: psql: what's the SQL to compute the ratio of table sizes?

On Fri, Oct 17, 2008 at 1:30 PM, Kynn Jones <kynnjo@gmail.com> wrote:
> Suppose I have two table X and Y and I want to compute the ratio of the
> number of rows in X and the number of rows in Y.  What would be the SQL I
> could type into a psql session to get this number?
> This is an example of the recurring problem of performing arithmetic using
> the result of various calls to count(*) (or other mathematic functions).

do you need exact or approximate answers for the number of rows?

On Oct 17, 2008, at 12:30 PM, Kynn Jones wrote:

> Suppose I have two table X and Y and I want to compute the ratio of
> the number of rows in X and the number of rows in Y.  What would be
> the SQL I could type into a psql session to get this number?
>
> This is an example of the recurring problem of performing arithmetic
> using the result of various calls to count(*) (or other mathematic
> functions).

select (select count(*)::float from X) / (select count(*)::float from
Y);

count(*) may take a while on large tables; there are other options if
you'd prefer something fast and approximate.

Cheers,
   Steve

On Fri, Oct 17, 2008 at 3:38 PM, Kynn Jones <kynnjo@gmail.com> wrote:
> Thanks for all your suggestions!
> Kynn

If an approximate count will work take a look in pg_stat_user_tables
right after a vacuum.  It's much faster.

On Fri, 2008-10-17 at 15:30 -0400, Kynn Jones wrote:
> Suppose I have two table X and Y and I want to compute the ratio of
> the number of rows in X and the number of rows in Y.  What would be
> the SQL I could type into a psql session to get this number?

Sub-selects should work.  And make sure to cast to avoid integer
division (well, assuming you want to avoid it...)

SELECT (SELECT COUNT(*) FROM tablex)::numeric / (SELECT COUNT(*) FROM
tabley)::numeric AS ratio;

- Josh Williams