From: | Dror Matalon <dror(at)zapatec(dot)com> |
---|---|
To: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Various performance questions |
Date: | 2003-10-27 04:54:31 |
Message-ID: | 20031027045431.GE2979@rlx11.zapatec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> Dror Matalon <dror(at)zapatec(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Christopher Browne | 2003-10-27 06:04:49 | Re: Various performance questions |
Previous Message | CHEWTC | 2003-10-27 04:27:29 | Duplicate in pg_user table |