For a very quick estimate:
SELECT reltuples FROM pg_class WHERE relname="my_table";
There are several caveats, though. For one,
relname is not necessarily unique in
pg_class. There can be multiple tables with the same
relname in multiple schemas of the database. To be unambiguous:
SELECT reltuples::bigint FROM pg_class WHERE oid = 'my_schema.my_table'::regclass;
If you do not schema-qualify the table name, a cast to
regclass observes the current
search_path to pick the best match. And if the table does not exist (or cannot be seen) in any of the schemas in the
search_path you get an error message. See Object Identifier Types in the manual.
The cast to
bigint formats the
real number nicely, especially for big counts.
reltuples can be more or less out of date. There are ways to make up for this to some extent. See this later answer with new and improved options:
- Fast way to discover the row count of a table in PostgreSQL
And a query on
pg_stat_user_tables is many times slower (though still much faster than full count), as that’s a view on a couple of tables.