From: Jesper Krogh <jesper@krogh.cc>
To: Tom Lane <tgl@sss.pgh.pa.us>
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: Tue, 12 Oct 2010 20:22:01 +0200
Message-ID: <4CB4A749.3080705@krogh.cc> (raw)
In-Reply-To: <27107.1286903278@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>
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
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: jesper@krogh.cc, tgl@sss.pgh.pa.us, mladen.gogala@vmsinfo.com, david@lang.hm, craig@postnewspapers.com.au, tivv00@gmail.com
Subject: Re: Slow count(*) again...
In-Reply-To: <4CB4A749.3080705@krogh.cc>
* 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