Re: count * performance issue

On Thu, 06 Mar 2008 07:28:50 -0800
Craig James <craig_james(at)emolecules(dot)com> 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(at)druid(dot)net> | 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.