How do I speed up counting rows in a PostgreSQL table?

by

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.

Also, 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:

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.

More Related Contents:

  1. Optimize GROUP BY query to retrieve latest row per user
  2. Any downsides of using data type “text” for storing strings?
  3. Fast way to discover the row count of a table in PostgreSQL
  4. Query with LEFT JOIN not returning rows for count of 0
  5. Way to try multiple SELECTs till a result is available?
  6. Finding similar strings with PostgreSQL quickly
  7. Compute percents from SUM() in the same SELECT sql query
  8. Best way to delete millions of rows by ID
  9. Postgres not using index when index scan is much better option
  10. Conditional SQL count
  11. SELECT DISTINCT is slower than expected on my table in PostgreSQL
  12. Run a query with a LIMIT/OFFSET and also get the total number of rows
  13. Query for count of distinct values in a rolling date range
  14. How to declare a variable in a PostgreSQL query
  15. How do I force Postgres to use a particular index?
  16. Record returned from function has columns concatenated
  17. Query for element of array in JSON column
  18. INSERT rows into multiple tables in a single query, selecting from an involved table
  19. Casting NULL type when updating multiple rows
  20. How to find a table having a specific column in postgresql
  21. How to read the contents of an .sql file into an R script to run a query?
  22. PostgreSQL JOIN with array type with array elements order, how to implement?
  23. Trim trailing spaces with PostgreSQL
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Check whether string is a date Postgresql
  26. Counting the number of occurrences of a substring within a string in PostgreSQL
  27. SQL INSERT without specifying columns. What happens?
  28. How to compare dates in datetime fields in Postgresql?
  29. How to perform a select query in a DO block?
  30. Cannot SELECT from UPDATE RETURNING clause in postgres

Leave a Comment