Message-ID: <4CB4A749.3080705@krogh.cc>
Date: Tue, 12 Oct 2010 20:22:01 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.12) Gecko/20100915 Thunderbird/3.0.8
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
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>
Subject: Re: Slow count(*) again...
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>
In-Reply-To: <27107.1286903278@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2010-10-12 19:07, Tom Lane wrote:
> Anyway, if anyone is hot to make COUNT(*) faster, that's where to look.
> I don't think any of the previous discussion in this thread is on-point
> at all, except for the parts where people suggested avoiding it.
>    

I would have to say that allthough it is nice to get count(*) faster I
think your testing is way too simple.

It pretty much proves that in terms of the code involved in the
count(*) process there is not much to be achieved. But your table
has way to little payload. As PG currently is it will start by pushing
data off to TOAST when the tuple size reaches 1KB
and the speed of count(*) is very much dominated by the amount
of "dead weight" it has to draw in together with the heap-access for the
row on accessing the table. Creating a case where the table is this
slim is (in my viewpoint) very much to the extreme on the small side.

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.

.. without having complete insigt.. a visibillity map that could be used in
conjunction with indices would solve that. What the cost would be
of maintaining it is also a factor.

Jesper

-- 
Jesper