What is the most efficient way to get a row count of all tables in my database?
I'm using a Postgres database.
Example Result
table_name row_count
------------ -------------
some_table 1,234
foobar 5,678
another_table 32
...
-
the number of rows in that table...Don P– Don P2015-02-23 06:22:51 +00:00Commented Feb 23, 2015 at 6:22
-
1Please look here: row count in postgres and sysobjects in postgresHockenberry– Hockenberry2015-02-23 06:36:22 +00:00Commented Feb 23, 2015 at 6:36
-
Thanks @Hockenberry - strange that it didn't show up in search for me! I'd close this question, but it has an answerDon P– Don P2015-02-23 06:50:07 +00:00Commented Feb 23, 2015 at 6:50
-
I am using this tool which has its own command for thisuser330315– user3303152015-02-23 07:30:04 +00:00Commented Feb 23, 2015 at 7:30
Add a comment
|
2 Answers
if you want a perticular table's rowcount then it will work
SELECT reltuples FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;
reltuples is a column from pg_class table, it holds data about "number of rows >in the table. This is only an estimate used by the planner.
and if your want a list of all tables with its rowcount then it will do the job
SELECT
pgClass.relname AS tableName,
pgClass.reltuples AS rowCount
FROM
pg_class pgClass
INNER JOIN
pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pgClass.relkind='r'
"Why is "SELECT count(*) FROM bigtable;" slow?" : count(*)
2 Comments
Frank Heikens
Why a LEFT JOIN when you make it an INNER JOIN?
wanaryytel
Note that you might have to run the
ANALYZE command on your db before this gives correct results. For me there was a disparity between SELECT count(*) FROM table; and the results this query gave me for the table. Until I ran ANALYZE.For total row count of entire database use this
SELECT
SUM(pgClass.reltuples) AS totalRowCount
FROM
pg_class pgClass
LEFT JOIN
pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pgClass.relkind='r'
And the row counts for the specific tables in the same database go for this
SELECT
pgClass.relname AS tableName,
pgClass.reltuples AS rowCount
FROM
pg_class pgClass
LEFT JOIN
pg_namespace pgNamespace ON (pgNamespace.oid = pgClass.relnamespace)
WHERE
pgNamespace.nspname NOT IN ('pg_catalog', 'information_schema') AND
pgClass.relkind='r'
For reference here is the link
1 Comment
Richard Fearn
One caveat to this (from the documentation): "This is only an estimate used by the planner."