"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Greg Stark wrote: > > >Thomas Zehetbauer <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])