pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: D'Arcy J.M. Cain <darcy@druid.net>
To: Craig James <craig_james@emolecules.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: count * performance issue
Date: Thu, 6 Mar 2008 11:31:44 -0500
Message-ID: <20080306113144.ed231755.darcy@druid.net> (raw)
In-Reply-To: <47D00DB2.7040007@emolecules.com>
References: <f966c2ee0803052142l6a105678jfe2da92a0d9371@mail.gmail.com>
	<f966c2ee0803052143k4570d771o6c300647d3b987d@mail.gmail.com>
	<20080306060829.GA21084@a-kretschmer.de>
	<bf54be870803052215y5bb56235k5ea05ada1d701022@mail.gmail.com>
	<47CF8EA6.8070503@mark.mielke.cc>
	<20080306063643.GB21084@a-kretschmer.de>
	<f966c2ee0803052243w723e2866gbfc37128620d1c6d@mail.gmail.com>
	<B94D3180-BB72-43EB-BA1E-90CF53705386@fastcrypt.com>
	<f966c2ee0803060358k14dd7374h8ef3f4ca0818b04@mail.gmail.com>
	<3E9C3A51-C28E-4B75-9739-E344DAC72880@fastcrypt.com>
	<f966c2ee0803060443j3d9c1b66x5f59e00d084e841c@mail.gmail.com>
	<Pine.GSO.4.64.0803060813570.11897@westnet.com>
	<47D00DB2.7040007@emolecules.com>

On Thu, 06 Mar 2008 07:28:50 -0800
Craig James <craig_james@emolecules.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@druid.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.



view thread (62+ messages)  latest in thread

Message-ID: <20080306113144.ed231755.darcy@druid.net>
Permalink:  ../20080306113144.ed231755.darcy@druid.net/
Also on:    postgresql.org/message-id/20080306113144.ed231755.darcy@druid.net

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: darcy@druid.net, craig_james@emolecules.com
  Subject: Re: count * performance issue
  In-Reply-To: <20080306113144.ed231755.darcy@druid.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox