Re: Various performance questions

On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
> dror(at)zapatec(dot)com (Dror Matalon) wrote:
> > 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.
>
> Most of the time involves:
>
> a) Reading each page of the table, and
> b) Figuring out which records on those pages are still "live."

The table has been VACUUM ANALYZED so that there are no "dead" records.
It's still not clear why select count() would be slower than select with
a "where" clause.

>
> What work were you thinking was involved in doing the counting?

I was answering an earlier response that suggested that maybe the actual
counting took time so it would take quite a bit longer when there are
more rows to count.

>
> >> 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.
>
> Good; at least that's consistent...
> --
> (format nil "~S(at)~S" "cbbrowne" "acm.org")
> http://www3.sympatico.ca/cbbrowne/postgresql.html
> Signs of a Klingon Programmer #2: "You question the worthiness of my
> code? I should kill you where you stand!"
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

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