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

Re: count(*) slow on large tables

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Dror Matalon <dror(at)zapatec(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: count(*) slow on large tables
Date: 2003-10-02 19:58:43
Message-ID: 20031002195843.GA19021@wolff.to (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
On Thu, Oct 02, 2003 at 12:46:45 -0700,
  Dror Matalon <dror(at)zapatec(dot)com> wrote:

Please keep replies copied to the list.

> When would it happen that a tuple be invisible to the current
> transaction? Are we talking about permissions?

They could be tuples that were changed by a transaction that hasn't committed
or in the case of serializable isolation, a transaction that committed after
the current transaction started.

> 
> On Thu, Oct 02, 2003 at 02:39:05PM -0500, Bruno Wolff III wrote:
> > On Thu, Oct 02, 2003 at 12:15:47 -0700,
> >   Dror Matalon <dror(at)zapatec(dot)com> wrote:
> > > Hi,
> > > 
> > > I have a somewhat large table, 3 million rows, 1 Gig on disk,  and growing. Doing a
> > > count(*) takes around 40 seconds.
> > > 
> > > Looks like the count(*) fetches the table from disk and goes through it.
> > > Made me wonder, why the optimizer doesn't just choose the smallest index
> > > which in my case is around 60 Megs and goes through it, which it could
> > > do in a fraction of the time.
> > 
> > Because it can't tell from the index if a tuple is visible to the current
> > transaction and would still have to hit the table to check this. So that
> > performance would be a lot worse instead of better.
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> > 
> >                http://www.postgresql.org/docs/faqs/FAQ.html
> 
> -- 
> Dror Matalon, President
> Zapatec Inc 
> 1700 MLK Way
> Berkeley, CA 94709
> http://www.zapatec.com

In response to

pgsql-performance by date

Next:From: Rong WuDate: 2003-10-02 20:11:21
Subject: Thanks - Re: low cardinality column
Previous:From: scott.marloweDate: 2003-10-02 19:44:12
Subject: Re: TPC-R benchmarks

pgsql-hackers by date

Next:From: Tom LaneDate: 2003-10-02 20:00:46
Subject: Re: minor view creation weirdness
Previous:From: Neil ConwayDate: 2003-10-02 19:58:06
Subject: Re: minor view creation weirdness

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