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

Re: Slow count(*) again...

From: Mladen Gogala <mladen(dot)gogala(at)vmsinfo(dot)com>
To: Neil Whelchel <neil(dot)whelchel(at)gmail(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Slow count(*) again...
Date: 2010-10-11 23:50:36
Message-ID: 4CB3A2CC.9070009@vmsinfo.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
  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.

-- 
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com


In response to

Responses

pgsql-performance by date

Next:From: Robert HaasDate: 2010-10-12 00:52:23
Subject: Re: gist indexes for distance calculations
Previous:From: Damon SnyderDate: 2010-10-11 23:10:08
Subject: Stored procedure declared as VOLATILE => no good optimization is done

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-10-12 00:41:50
Subject: Re: Issues with two-server Synch Rep
Previous:From: Jeff DavisDate: 2010-10-11 23:16:50
Subject: Re: Issues with two-server Synch Rep

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