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 7D7D5D1B4EF for ; Mon, 27 Oct 2003 06:15:30 +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 91171-01 for ; Mon, 27 Oct 2003 02:15:00 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id A0471D1B4ED for ; Mon, 27 Oct 2003 02:14:59 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id h9R6ExNu043948 for ; Mon, 27 Oct 2003 06:14:59 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h9R6CQLo043824 for pgsql-performance@postgresql.org; Mon, 27 Oct 2003 06:12:26 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Various performance questions Date: Mon, 27 Oct 2003 01:04:49 -0500 Organization: cbbrowne Computing Inc Lines: 44 Message-ID: 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 X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1002 (Gnus v5.10.2) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:dAUux8RjFZFVLGCqScpOJmrLRAA= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/661 X-Sequence-Number: 4409 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." 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!"