Counting rows

It's non unusual to find an application that does the following to determine how many rows are in a table:

SELECT count(*) FROM t;

In some databases other than PostgreSQL, this executes very quickly, usually because that information is kept handy in an index or similar structure. Unfortunately, because PostgreSQL keeps its row visibility information in the row data pages, you cannot determine a true row count without looking at every row in the table, one at a time, to determine if they are visible or not. That's a sequential scan of the full table, and it's pretty slow; it even turns out to be an effective way to benchmark sequential read speed on a table to count its rows this way!

If your statement is fairly selective instead:

SELECT count(*) FROM t WHERE k>10 and k<20;

This form can execute quickly, presuming that the index scan on k is only returning a few rows. How fast a count runs is proportional to the number of rows returned.

If you...