On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> Dror Matalon <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to