> Here we have table "stats" with something over one millon records.
> Obvious "SELECT COUNT(*) FROM stats " takes over 40 seconds to execute,
> and this amount of time does not shorten considerably in subsequent
> similar requests. All the databases are vacuumed nightly.
Doing a row count requires a sequential scan in Postgres.
Try creating another summary table that just has one row and one column and
is an integer.
Then, create a trigger on your stats table that fires whenever a new row is
added or deleted and updates the tally of rows in the summary table.
Then, just select from the summary table to get an instantaneous count. Of
course, insert and deletes will be marginally slowed down.
Refer to the docs for CREATE TRIGGER, CREATE FUNCTION and PL/PGSQL for more
info on how to do this.
Regards,
Chris