Обсуждение: psql: what's the SQL to compute the ratio of table sizes?
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).
TIA!
Kynn
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
Thanks for all your suggestions!
Kynn
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