DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=dIPtq3hGp8VqNDY6/NM7xdX+5IkGkfJpzf22M2V75gqVdL0svExXFzfC815P+ybyBg
	QkjQZJ3iIairXdFqTXNmcCMeSQU2pW8L4qRh9Z0O1l0CKG9a0TtT0gDBReWBvmOPLsAS
	h4YUKJoU8SMRfXosBS7561w9fXlyNhAx1gkvg=
Message-ID: <4CB58FDB.3080505@gmail.com>
Date: Wed, 13 Oct 2010 13:54:19 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
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: "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>
	<4CB4A749.3080705@krogh.cc> <4292.1286909893@sss.pgh.pa.us>
In-Reply-To: <4292.1286909893@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

12.10.10 21:58, Tom Lane написав(ла):
>
> 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.
>
>    
I've though about "aggregate" indexes, something like
create index index_name on table_name(count(*) group by column1, column2);
OR
create index index_name on table_name(count(*));
for table-wide count

To make it usable one would need:
1) Allow third aggregate function SMERGE that can merge one aggregate 
state to another
2) The index should be regular index (e.g. btree) on column1, column2 
that for each pair has page list to which it's data may belong (in 
past/current running transactions), and aggregate state for each page 
that were frozen previously
When index is used, it can use precalculated values for "pages with all 
tuples vacuumed" (I suspect this is information from visibility map) and 
should do regular calculation for all non-frozen pages with visibility 
checks and everything what's needed.
When vacuum processes the page, it should (in sync or async way) 
calculate aggregate values for the page.

IMHO Such an indexes would make materialized views/triggers/high level 
caches unneeded in most cases.

Best regards, Vitalii Tymchyshyn