pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dror Matalon <dror@zapatec.com>
To: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: Various performance questions
Date: Sun, 26 Oct 2003 20:54:31 -0800
Message-ID: <20031027045431.GE2979@rlx11.zapatec.com> (raw)
In-Reply-To: <87u15vxrue.fsf@stark.dyndns.tv>
References: <20031026194449.GD2979@rlx11.zapatec.com>
	<87u15vxrue.fsf@stark.dyndns.tv>

On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> Dror Matalon <dror@zapatec.com> writes:
> 
> > explain analyze select count(*) from items where channel < 5000;
> >                                                         QUERY PLAN
> > --------------------------------------------------------------------------------------------------------------------------
> >  Aggregate  (cost=249141.54..249141.54 rows=1 width=0) (actual time=26224.603..26224.608 rows=1 loops=1)
> >    ->  Seq Scan on items  (cost=0.00..245377.52 rows=1505605 width=0) (actual time=7.599..17686.869 rows=1632057 loops=1)
> >          Filter: (channel < 5000)
> >  Total runtime: 26224.703 ms
> > 
> > 
> > How can it do a sequential scan and apply a filter to it in less time
> > than the full sequential scan? Is it actually using an index without
> > really telling me? 
> 
> It's not using the index and not telling you. 
> 
> It's possible the count(*) operator itself is taking some time. Postgres

I find it hard to believe that the actual counting would take a
significant amount of time.

> doesn't have to call it on the rows that don't match the where clause. How
> long does "explain analyze select 1 from items" with and without the where
> clause take?

Same as count(*). Around 55 secs with no where clause, around 25 secs
with.

> 
> What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an

This is 7.4.

> int8 to store its count so it's not limited to 4 billion records.
> Unfortunately int8 is somewhat inefficient as it has to be dynamically
> allocated repeatedly. It's possible it's making a noticeable difference,
> especially with all the pages in cache, though I'm a bit surprised. There's
> some thought about optimizing this in 7.5.
> 
> -- 
> greg
> 

-- 
Dror Matalon
Zapatec Inc 
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com



view thread (23+ messages)  latest in thread

Message-ID: <20031027045431.GE2979@rlx11.zapatec.com>
Permalink:  ../20031027045431.GE2979@rlx11.zapatec.com/
Also on:    postgresql.org/message-id/20031027045431.GE2979@rlx11.zapatec.com

 · 

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: dror@zapatec.com
  Subject: Re: Various performance questions
  In-Reply-To: <20031027045431.GE2979@rlx11.zapatec.com>

* 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