From: Scott Carey <scott@richrelevance.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jesper Krogh <jesper@krogh.cc>
Cc: Mladen Gogala <mladen.gogala@vmsinfo.com>
Cc: david@lang.hm <david@lang.hm>
Cc: Craig Ringer <craig@postnewspapers.com.au>
Cc: Vitalii Tymchyshyn <tivv00@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow count(*) again...
Date: Wed, 20 Oct 2010 21:47:24 -0700
Message-ID: <B54FDA83-1DBD-4313-AF44-EF53F3EB85D4@richrelevance.com> (raw)
In-Reply-To: <4292.1286909893@sss.pgh.pa.us>
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>
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.
>
>> 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.
>
>> 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.
>
> 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 query can go fast enough for them. The best I've gotten is 800MB/sec, on a wide 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 spring. I will easily be able to build a system with 2GB/sec I/O for under $10k.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott@richrelevance.com, tgl@sss.pgh.pa.us, jesper@krogh.cc, mladen.gogala@vmsinfo.com, david@lang.hm, craig@postnewspapers.com.au, tivv00@gmail.com
Subject: Re: Slow count(*) again...
In-Reply-To: <B54FDA83-1DBD-4313-AF44-EF53F3EB85D4@richrelevance.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox