Re: Select count(*) takes a long time

"Jeff Johnson" <jeff(at)jeffjohnson(dot)net> writes:
> think I had read that PostgreSQL, unlike most RDBMSs, does not store
> the current row count and so must be re-calculated on the fly.

Correct. This is one of the downsides of MVCC: there is no unique row
count, in general, so not much point in trying to keep track of it.

> particular situation I have a home page that must select a "featured"
> article by choosing one at random from a table of 300 thousand or so.

Doesn't seem like count(*) is an essential component of a solution to
this problem. What are the available article identifiers and indexes?
For example, if you had a timestamp column with an index, you could
define "a random article" as "the first one after a randomly chosen
time", which could be retrieved efficiently with

select * from articles where timestamp >= 'targeted time' limit 1;

The target time could be chosen as some random fraction between the
start of your database and now(). This'd be skewed by variations in
the rate of article posting, but it'd probably do for your purposes.

If there is a serial number column then it's even easier, since the
range of article numbers is from 1 to the sequence's last_value.

> I thought I came up with a good solution, now I "select ... from ...
> order by random() limit 1", which is nice because it only requires one
> query to get what I want but it's still slow.

That is most definitely *not* going to be fast, since it requires an
explicit sort of all the rows.

regards, tom lane