Re: count * performance issue

From: "D'Arcy J(dot)M(dot) Cain" <darcy(at)druid(dot)net>
To: Craig James <craig_james(at)emolecules(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: count * performance issue
Date: 2008-03-06 16:31:44
Message-ID: 20080306113144.ed231755.darcy@druid.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Bruce Momjian 2008-03-06 17:23:21 Re: Performance of aggregates over set-returning functions
Previous Message Joshua D. Drake 2008-03-06 16:28:49 Re: oid...any optimizations