On Thu, 06 Mar 2008 07:28:50 -0800 Craig James <craig_james@xxxxxxxxxxxxxx> wrote: > In the 3 years I've been using Postgres, the problem of count() performance has come up more times than I can recall, and each time the answer is, "It's a sequential scan -- redesign your application." > > My question is: What do the other databases do that Postgres can't do, and why not? > > Count() on Oracle and MySQL is almost instantaneous, even for very large tables. So why can't Postgres do what they do? It's a tradeoff. The only way to get that information quickly is to maintain it internally when you insert or delete a row. So when do you want to take your hit. It sounds like Oracle has made this decision for you. In PostgreSQL you can use triggers and rules to manage this information if you need it. You can even do stuff like track how many of each type of something you have. That's something you can't do if your database engine has done a generic speedup for you. You would still have to create your own table for something like that and then you get the hit twice. -- D'Arcy J.M. Cain <darcy@xxxxxxxxx> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-performance
