How many rows do I have anyway?
Posted Thursday Aug 23rd, 2007 12:19pm
by Joshua Drake | Permalink



Have you ever wondered how many tuples you have in a relation? Normally to find out how many tuples you have you would do something like:

SELECT count(1) FROM rows;
 count 
-------
 10002

This will return the exact number, per your snapshot of committed tuples within a relation. It is also a guaranteed sequence scan on the relation and a performance nightmare on large tables. So how do you get around it? Well it depends, if you need the exact amount of tuples within the relation, you don't. However if you only need an approximate number you can do this:

SELECT reltuples FROM pg_class 
   JOIN pg_namespace ON (pg_class.relnamespace = pg_namespace.oid) 
WHERE nspname = '$namespace' 
AND relname = '$tablename';
 reltuples 
-----------
     10002

The above will result in the exact number of tuples that PostgreSQL knows to be in the relation. Q. How does PostgreSQL know how many tuples are in a relation? A. Why ANALYZE of course. When you ANALYZE the pg_class relation is updated with vital and directly pertinent information about your relations. Yet another reason to beat people over the head with the, WHAT EVERY POSTGRESQL USER NEEDS TO KNOW.

---

blog comments powered by Disqus