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 70D48D1B4ED for ; Mon, 27 Oct 2003 04:55:18 +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 65448-07 for ; Mon, 27 Oct 2003 00:54:47 -0400 (AST) Received: from five.zapatec.com (66-117-150-100.web.lmi.net [66.117.150.100]) by svr1.postgresql.org (Postfix) with ESMTP id 10A25D1B4E1 for ; Mon, 27 Oct 2003 00:54:47 -0400 (AST) Received: from rlx11.zapatec.com (rlx11.pr.zapatec.com [192.168.1.132]) by five.zapatec.com (Postfix) with ESMTP id C65F55D20 for ; Sun, 26 Oct 2003 20:54:31 -0800 (PST) Received: (from dror@localhost) by rlx11.zapatec.com (8.12.3/8.12.3/Submit) id h9R4sVNQ067273 for pgsql-performance@postgresql.org; Sun, 26 Oct 2003 20:54:31 -0800 (PST) (envelope-from dror) Date: Sun, 26 Oct 2003 20:54:31 -0800 From: Dror Matalon To: Postgresql Performance Subject: Re: Various performance questions Message-ID: <20031027045431.GE2979@rlx11.zapatec.com> References: <20031026194449.GD2979@rlx11.zapatec.com> <87u15vxrue.fsf@stark.dyndns.tv> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <87u15vxrue.fsf@stark.dyndns.tv> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/660 X-Sequence-Number: 4408 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. > 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. > > What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an This is 7.4. > int8 to store its count so it's not limited to 4 billion records. > Unfortunately int8 is somewhat inefficient as it has to be dynamically > allocated repeatedly. It's possible it's making a noticeable difference, > especially with all the pages in cache, though I'm a bit surprised. There's > some thought about optimizing this in 7.5. > > -- > greg > -- Dror Matalon Zapatec Inc 1700 MLK Way Berkeley, CA 94709 http://www.zapatec.com