[sNip]
>> I would add that this is not a bug as much as a feature request.
>> count() works. It may not be as feature
>> filled as we would like (e.g; it won't use an index) but it does work.
>
> count will use an index just fine where it's useful. If you say "select
> count(*) where foo = ?" and there's an index on foo it will use the
> index. If there's a partial index that helps with that clause it'll
> consider that too.
>
> You're thinking of min/max. min/max can use an index to avoid traversing
> all of the table. count(*) has to see all the rows to count them.
>
> To optimize count effectively would require a very powerful materalized
> view infrastructure with incremental updates. Something I don't believe
> any database has, and that I doubt postgres will get any time soon.
>
> You can implement it with triggers, which would be effectively
> equivalent to what mysql does, but then you would be introducing a
> massive point of contention and deadlocks.
What about adding a "total number of rows" value to the internal
header of each table which gets incremented/decremented after each row is
INSERT/DELETE has been committed. This way, a generic "count(*)" by itself
could simply return this value without any delay at all.
--
Randolf Richardson - rr@8x.ca
Vancouver, British Columbia, Canada
Please do not eMail me directly when responding
to my postings in the newsgroups.