From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: Various performance questions
Date: Mon, 27 Oct 2003 01:04:49 -0500
Message-ID: <m3r80zmd1a.fsf@wolfe.cbbrowne.com> (raw)
References: <20031026194449.GD2979@rlx11.zapatec.com>
<87u15vxrue.fsf@stark.dyndns.tv>
<20031027045431.GE2979@rlx11.zapatec.com>
dror@zapatec.com (Dror Matalon) wrote:
> 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.
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@~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!"
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: cbbrowne@acm.org
Subject: Re: Various performance questions
In-Reply-To: <m3r80zmd1a.fsf@wolfe.cbbrowne.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