From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow count(*) again...
Date: Wed, 13 Oct 2010 13:54:19 +0300
Message-ID: <4CB58FDB.3080505@gmail.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>
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
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: tivv00@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Slow count(*) again...
In-Reply-To: <4CB58FDB.3080505@gmail.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