X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 584259DC819 for ; Thu, 23 Feb 2006 16:45:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 67112-08 for ; Thu, 23 Feb 2006 16:45:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 0B5389DCA20 for ; Thu, 23 Feb 2006 16:25:18 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 114BD56423; Thu, 23 Feb 2006 14:25:49 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Thu, 23 Feb 2006 14:25:46 -0600 Date: Thu, 23 Feb 2006 14:25:46 -0600 From: "Jim C. Nasby" To: Kevin Grittner Cc: Greg Stark , pgsql-performance@postgresql.org Subject: Re: Good News re count(*) in 8.1 Message-ID: <20060223202546.GP86022@pervasive.com> References: <43FC4384.EE98.0025.0@wicourts.gov> <87irr6zq7j.fsf@stark.xeocode.com> <43FDB09C.EE98.0025.0@wicourts.gov> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43FDB09C.EE98.0025.0@wicourts.gov> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060223:kevin.grittner@wicourts.gov::xh/ZGsTrwfA5MQUp:0000000000 000000000000000000000000GzKf X-Hashcash: 1:20:060223:gsstark@mit.edu::ZEY3sZmrp5ObUZ5E:00CNiW X-Hashcash: 1:20:060223:pgsql-performance@postgresql.org::c8Rojs172II20DVi:00000 00000000000000000000000009D4 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.105 required=5 tests=[AWL=0.105] X-Spam-Score: 0.105 X-Spam-Level: X-Archive-Number: 200602/376 X-Sequence-Number: 17362 On Thu, Feb 23, 2006 at 12:54:52PM -0600, Kevin Grittner wrote: > >>> On Wed, Feb 22, 2006 at 9:52 pm, in message > <87irr6zq7j.fsf@stark.xeocode.com>, Greg Stark wrote: > > > > "Kevin Grittner" writes: > > > >> There have been several times that I have run a SELECT COUNT(*) on > an entire > >> table on all central machines. On identical hardware, with identical > data, > >> and equivalent query loads, the PostgreSQL databases have responded > with a > >> count in 50% to 70% of the time of the commercial product, in spite > of the > >> fact that the commercial product does a scan of a non- clustered > index while > >> PostgreSQL scans the data pages. > > > > I take it these are fairly narrow rows? The big benefit of index- > only scans > > come in when you're scanning extremely wide tables, often counting > rows > > matching some indexed criteria. > > I'm not sure what you would consider "fairly narrow rows" -- so see the > attached. This is the VACUUM ANALYZE VERBOSE output for the largest > table, from last night's regular maintenance run. Looks to be about 60 rows per page, somewhere around 140 bytes per row (including overhead). Accounting for overhead and allowing for some empty room, about 100 bytes of data per row, which isn't all that thin. Not all that fat, either... The PK index is about 5 times smaller. IF that ratio holds on the commercial product and they can't beat us with an index scan.... :) -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461