Re: Various performance questions

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

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

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