Message-ID: <4CB41902.1020503@postnewspapers.com.au>
Date: Tue, 12 Oct 2010 16:14:58 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.9) Gecko/20100921 Fedora/3.1.4-1.fc14 Thunderbird/3.1.4
MIME-Version: 1.0
To: Vitalii Tymchyshyn <tivv00@gmail.com>
CC: 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>
In-Reply-To: <4CB4149D.8030803@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 10/12/2010 03:56 PM, Vitalii Tymchyshyn wrote:

> BTW: There is a lot of talk about MVCC, but is next solution possible:
> 1) Create a page information map that for each page in the table will
> tell you how may rows are within and if any write (either successful or
> not) were done to this page. This even can be two maps to make second
> one really small (a bit per page) - so that it could be most time
> in-memory.
> 2) When you need to to count(*) or index check - first check if there
> were no writes to the page. If not - you can use count information from
> page info/index data without going to the page itself
> 3) Let vacuum clear the bit after frozing all the tuples in the page (am
> I using terminology correctly?).

Part of this already exists. It's called the visibility map, and is 
present in 8.4 and above. It's not currently used for queries, but can 
potentially be used to aid some kinds of query.

http://www.postgresql.org/docs/8.4/static/storage-vm.html

> In this case all read-only (archive) data will be this bit off and
> index/count(*) will be really fast.

A count with any joins or filter criteria would still have to scan all 
pages with visible tuples in them. So the visibility map helps speed up 
scanning of bloated tables, but doesn't provide a magical "fast count" 
except in the utterly trivial "select count(*) from tablename;" case, 
and can probably only be used for accurate results when there are no 
read/write transactions currently open. Even if you kept a count of 
tuples in each page along with the mvcc transaction ID information 
required to determine for which transactions that count is valid, it'd 
only be useful if you didn't have to do any condition checks, and it'd 
be yet another thing to update with every insert/delete/update.

Perhaps for some users that'd be worth having, but it seems to me like 
it'd have pretty narrow utility. I'm not sure that's the answer.

--
Craig Ringer