Re: does "select count(*) from mytable" always do a seq

On Fri, Jan 07, 2005 at 16:17:16 +0100,
Tino Wildenhain <tino(at)wildenhain(dot)de> wrote:
> Am Freitag, den 07.01.2005, 06:45 -0800 schrieb Culley Harrelson:
> > Hi,
> >
> > I am using Postgresql 7.4. I have a table with 1.5 million rows. It
> > has a primary key. VACUUM FULL ANALYZE is run every night. There are
> > 2000-5000 inserts on this table every day but very few updates and
> > deletes. When I select count(*) from this table it is using a
> > sequence scan. Is this just life or is there some way to get this to
> > do an index scan?
>
> How do you think an index would help if you do an unconditional
> count(*)?

Some systems can just run through the index without having to access the
tuples. This can result in you having to read significantly fewer disk blocks
to get the count. Unfortunately, postgres still needs to check visibility
for each tuple and so an using index scan for count will be slower than
a sequential scan if a significant fraction of the table is being counted.

If an approximate answer is OK there is some information calculated when
you vacuum a table and you could query this value in the pg catalog.
I don't remember the name of what you want, but this should be in the
archives.

Another solution is to use a trigger to keep a count in another table.
from what you say above, this might be a practical solution for you.
Doing this has also been discussed in the archives.