"Joshua D. Drake" <jd@commandprompt.com> writes:
> Greg Stark wrote:
>
> >Thomas Zehetbauer <thomasz@hostmaster.org> writes:
> >
> >
> >>Also will the BUG which causes postgresql to execute a sequential scan
> >>when using min()/max()/count() ever be fixed? min()/max() can be
> >>rewritten as SELECT $column ORDER BY $column ASC/DESC LIMIT 1 but this
> >>should be done by the database, NOT by the user!
> >>
>
> 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.
--
greg