From: Scott Carey <scott@richrelevance.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Jesper Krogh <jesper@krogh.cc>, Mladen Gogala <mladen.gogala@vmsinfo.com>,
	"david@lang.hm" <david@lang.hm>, Craig Ringer <craig@postnewspapers.com.au>,
	Vitalii Tymchyshyn <tivv00@gmail.com>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Date: Wed, 20 Oct 2010 21:47:24 -0700
Subject: Re: Slow count(*) again... 
Thread-Topic: [PERFORM] Slow count(*) again... 
Thread-Index: Actw2uJoCLD83afISNWl/nqjTWd9yg==
Message-ID: <B54FDA83-1DBD-4313-AF44-EF53F3EB85D4@richrelevance.com>
References: <201010091626.18636.neil.whelchel@gmail.com>
	<AANLkTimayTM__hHu9RoiLaNhzfpNY6Vo5y4OkbX_sV=o@mail.gmail.com>
	<4CB34D69.6060108@emolecules.com> <4CB4149D.8030803@gmail.com>
	<4CB41902.1020503@postnewspapers.com.au>
	<alpine.DEB.2.00.1010120118030.21889@asgard.lang.hm>
	<4CB4542E.5010406@vmsinfo.com> <23516.1286891790@sss.pgh.pa.us>
	<4CB46AE2.7030006@vmsinfo.com> <27107.1286903278@sss.pgh.pa.us>
	<4CB4A749.3080705@krogh.cc> <4292.1286909893@sss.pgh.pa.us>
In-Reply-To: <4292.1286909893@sss.pgh.pa.us>
Accept-Language: en-US
Content-Language: en-US
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


On Oct 12, 2010, at 11:58 AM, Tom Lane wrote:

> Jesper Krogh <jesper@krogh.cc> writes:
>> On 2010-10-12 19:07, Tom Lane wrote:
>>> Anyway, if anyone is hot to make COUNT(*) faster, that's where to look.
>=20
>> Just having 32 bytes bytes of "payload" would more or less double
>> you time to count if I read you test results correctly?. .. and in the
>> situation where diskaccess would be needed .. way more.
>=20
>> Dividing by pg_relation_size by the amout of tuples in our production
>> system I end up having no avg tuple size less than 100bytes.
>=20
> Well, yeah.  I deliberately tested with a very narrow table so as to
> stress the per-row CPU costs as much as possible.  With any wider table
> you're just going to be I/O bound.


On a wimpy disk, I/O bound for sure.   But my disks go 1000MB/sec.  No quer=
y can go fast enough for them.  The best I've gotten is 800MB/sec, on a wid=
e row (average 800 bytes).  Most tables go 300MB/sec or so.  And with 72GB =
of RAM, many scans are in-memory anyway.

A single SSD with supercapacitor will go about 500MB/sec by itself next spr=
ing.   I will easily be able to build a system with 2GB/sec I/O for under $=
10k.