pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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!"



view thread (23+ messages)  latest in thread

Message-ID: <m3r80zmd1a.fsf@wolfe.cbbrowne.com>
Permalink:  ../m3r80zmd1a.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m3r80zmd1a.fsf@wolfe.cbbrowne.com

 · 

reply

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