From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Tue, 12 Oct 2010 14:58:13 -0400
Message-ID: <4292.1286909893@sss.pgh.pa.us> (raw)
In-Reply-To: <4CB4A749.3080705@krogh.cc>
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>
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.
> .. 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.
I'm less than convinced that that approach will result in a significant
win. It's certainly not going to do anything to convert COUNT(*) into
an O(1) operation, which frankly is what the complainants are expecting.
There's basically no hope of solving the "PR problem" without somehow
turning COUNT(*) into a materialized-view reference. We've discussed
that in the past, and know how to do it in principle, but the complexity
and distributed overhead are daunting.
regards, tom lane
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: 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: <4292.1286909893@sss.pgh.pa.us>
* 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