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
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