Skip site navigation (1) Skip section navigation (2)

Re: Slow count(*) again...

From: Neil Whelchel <neil(dot)whelchel(at)gmail(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow count(*) again...
Date: 2010-10-12 02:36:45
Message-ID: 201010111936.46207.neil.whelchel@gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Monday 11 October 2010 16:50:36 you wrote:
>   On 10/11/2010 3:54 PM, Neil Whelchel wrote:
> > 1. A faster count(*), or something like my proposed estimate(*).
> > 2. A way to get the total rows matched when using LIMIT and OFFSET before
> > LIMIT and OFFSET are applied.
> 
> The biggest single problem with "select count(*)" is that it is
> seriously overused. People use that idiom to establish existence, which
> usually leads to a performance disaster in the application using it,
> unless the table has no more than few hundred records. SQL language, of
> which PostgreSQL offers an excellent implementation,  offers [NOT]
> EXISTS clause since its inception in the Jurassic era. The problem is
> with the sequential scan, not with counting. I'd even go as far as to
> suggest that 99% instances of the "select count(*)" idiom are probably
> bad use of the SQL language.

I agree, I have seen many very bad examples of using count(*). I will go so 
far as to question the use of count(*) in my examples here. It there a better 
way to come up with a page list than using count(*)? What is the best method 
to make a page of results and a list of links to other pages of results? Am I 
barking up the wrong tree here?
-Neil-

In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-10-12 02:59:28
Subject: Re: How does PG know if data is in memory?
Previous:From: Mladen GogalaDate: 2010-10-12 02:23:46
Subject: Re: Slow count(*) again...

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-12 02:39:41
Subject: Re: Issues with two-server Synch Rep
Previous:From: Robert HaasDate: 2010-10-12 02:35:59
Subject: Re: security hook on table creation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group