> Some of the people try and get burned because features that MySQL did without anyone noticing now take so much time that one wonders if he triggered some strange bug that caused db to do full table scan where one is unnecessary. Then he goes to the internet and look for clues and he sees response "oh, it's how it supposed to work, nobody is working on because it does not seem like a problem" over and over.
Just about every non-trivial database implementation will have a pathology that others do not. In this case, InnoDB is was faster because it supported index-only scans, a feature that was very useful, but hard to implement in PG because of some details of its MVCC implementation -- now there is an implementation in 9.2 that gives it a shot at being in the same class of performance even on wide-row tables. However, both are still much slower than MyISAM: if you search for "why is count slow on innodb?" you'll get a lot of hits -- it suffers much the same defect vs. MyISAM (which has poor SMP concurrency, allowing it to count things as a luxury) that Postgres does.
Perhaps "not a problem" is a lazy answer for "it's hard to get exactly right, and nobody in the same class of implementation is bothering, and this has been discussed to death and is more work than you realize, so don't expect anyone to implement it soon unless you intend to argue it (unless you are also a long-term maintainer) and do it." As you can see...that was many more words, including giving background on two MySQL storage engines.
> Do you know why db can't just keep track of number of entries in a full or partial primary key indexes and use them to give count fast ?
It could. But you'd have to spec out a brand new top-level set of utility commands (like CREATE INDEX) to make this physical structure, it now is yet another knob that can drastically change performance characteristics, the planner gets to scan the projections and qualifiers for yet another little optimization, someone else gets to maintain it (unless you are maintainer), and then there's going to be the person that asks "why isn't is this snapshot-isolated version not even nearly as fast as caching the count in memcached?" (or, if one does it the other way, the reverse), and then finally when you get around to implementing a more general set of functionality you'll be stuck with this old form for basically eternity (depending on your release policy) much like hash indexes. Don't forget to add backup-and-restore support, manual pages, and EXPLAIN and/or other diagnostic output, if necessary.
On the plus side, it could end the endless howling about this problem.
Personally, I think we can get enough hooks in place that someone should be able to implement this secondary structure in a satisfying way without coupling it inextricably into the database, and that's a project I'd have enthusiasm for.
If someone else made this feature in another popular database that serves similar use cases and it got used a lot, then I think the understanding of the upsides would be such that such a feature is more likely to happen. Index-scans fall into that category, and a special structure not seen in other databases doesn't appear to at this time.
Just about every non-trivial database implementation will have a pathology that others do not. In this case, InnoDB is was faster because it supported index-only scans, a feature that was very useful, but hard to implement in PG because of some details of its MVCC implementation -- now there is an implementation in 9.2 that gives it a shot at being in the same class of performance even on wide-row tables. However, both are still much slower than MyISAM: if you search for "why is count slow on innodb?" you'll get a lot of hits -- it suffers much the same defect vs. MyISAM (which has poor SMP concurrency, allowing it to count things as a luxury) that Postgres does.
Perhaps "not a problem" is a lazy answer for "it's hard to get exactly right, and nobody in the same class of implementation is bothering, and this has been discussed to death and is more work than you realize, so don't expect anyone to implement it soon unless you intend to argue it (unless you are also a long-term maintainer) and do it." As you can see...that was many more words, including giving background on two MySQL storage engines.
> Do you know why db can't just keep track of number of entries in a full or partial primary key indexes and use them to give count fast ?
It could. But you'd have to spec out a brand new top-level set of utility commands (like CREATE INDEX) to make this physical structure, it now is yet another knob that can drastically change performance characteristics, the planner gets to scan the projections and qualifiers for yet another little optimization, someone else gets to maintain it (unless you are maintainer), and then there's going to be the person that asks "why isn't is this snapshot-isolated version not even nearly as fast as caching the count in memcached?" (or, if one does it the other way, the reverse), and then finally when you get around to implementing a more general set of functionality you'll be stuck with this old form for basically eternity (depending on your release policy) much like hash indexes. Don't forget to add backup-and-restore support, manual pages, and EXPLAIN and/or other diagnostic output, if necessary.
On the plus side, it could end the endless howling about this problem.
Personally, I think we can get enough hooks in place that someone should be able to implement this secondary structure in a satisfying way without coupling it inextricably into the database, and that's a project I'd have enthusiasm for.
If someone else made this feature in another popular database that serves similar use cases and it got used a lot, then I think the understanding of the upsides would be such that such a feature is more likely to happen. Index-scans fall into that category, and a special structure not seen in other databases doesn't appear to at this time.