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 0E3DFD1B50E for ; Mon, 27 Oct 2003 03:50:12 +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 64093-04 for ; Sun, 26 Oct 2003 23:49:44 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id 6BA0AD1B50C for ; Sun, 26 Oct 2003 23:49:40 -0400 (AST) Received: from stark.dyndns.tv (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id A19DD369C6; Sun, 26 Oct 2003 22:49:29 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 1ADyNl-0003AL-00; Sun, 26 Oct 2003 22:49:29 -0500 To: Dror Matalon Cc: Postgresql Performance Subject: Re: Various performance questions References: <20031026194449.GD2979@rlx11.zapatec.com> In-Reply-To: <20031026194449.GD2979@rlx11.zapatec.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 26 Oct 2003 22:49:29 -0500 Message-ID: <87u15vxrue.fsf@stark.dyndns.tv> Lines: 31 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/658 X-Sequence-Number: 4406 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 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? What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an 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