Re: index does not improve performance

Milos Prudek <milos(dot)prudek(at)tiscali(dot)cz> writes:
> think that FoxPro was able to return COUNT immediately, if index was
> used, no matter what number of records the condition was true for. I
> wonder if this is something that PostgreSQL will eventually be able to
> do, or if it is simply not technically possible for some reason.

It is not going to happen in the foreseeable future, for the simple
reason that there isn't necessarily a uniquely correct COUNT value
in Postgres' view of the world, and thus no prospect of maintaining
a single counter that would be of any use. You may care to read the
MVCC chapter of the manual,

http://developer.postgresql.org/docs/postgres/mvcc.html

Also see my talk on Postgres transactions, a PDF available at

http://developer.postgresql.org/osdn.php

This is a bit out of date (the comments about crash-safeness predate
the addition of WAL) but the discussion of tuple visibility is still
very relevant.

regards, tom lane