X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 35DDFD1B4F9 for ; Mon, 27 Oct 2003 07:17:34 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 92690-05 for ; Mon, 27 Oct 2003 03:17:04 -0400 (AST) Received: from rlx13.zapatec.com (66-117-144-213.zapatec.lmi.net [66.117.144.213]) by svr1.postgresql.org (Postfix) with ESMTP id 5978FD1B4E1 for ; Mon, 27 Oct 2003 03:17:03 -0400 (AST) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by rlx13.zapatec.com (Postfix) with ESMTP id D6594A941 for ; Sun, 26 Oct 2003 23:17:04 -0800 (PST) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h9R7H3Ii067522 for pgsql-performance@postgresql.org; Sun, 26 Oct 2003 23:17:03 -0800 (PST) (envelope-from dror) Date: Sun, 26 Oct 2003 23:17:03 -0800 From: Dror Matalon To: pgsql-performance@postgresql.org Subject: Re: Various performance questions Message-ID: <20031027071703.GF2979@rlx11.zapatec.com> References: <20031026194449.GD2979@rlx11.zapatec.com> <87u15vxrue.fsf@stark.dyndns.tv> <20031027045431.GE2979@rlx11.zapatec.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/663 X-Sequence-Number: 4411 On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote: > dror@zapatec.com (Dror Matalon) wrote: > > On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote: > >> Dror Matalon 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@~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