Message-ID: <474FB0B1.6070900@pws.com.au>
Date: Fri, 30 Nov 2007 17:41:53 +1100
From: Russell Smith <mr-russ@pws.com.au>
User-Agent: Thunderbird 2.0.0.6 (X11/20071022)
MIME-Version: 1.0
To: Simon Riggs <simon@2ndquadrant.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: TB-sized databases
References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com>	
	<474B1407.3090607@laotraesquina.com.ar>
	<474B1B8C.1030505@gmail.com>	
	<4544e0330711271218h56ce4cc3p280a9fefff24583d@mail.gmail.com>	
	<1196197045.4246.1154.camel@ebony.site>	
	<474CA2FA.2020201@laotraesquina.com.ar>
	<1196270908.4246.1250.camel@ebony.site>
In-Reply-To: <1196270908.4246.1250.camel@ebony.site>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Simon Riggs wrote:
> On Tue, 2007-11-27 at 18:06 -0500, Pablo Alcaraz wrote:
>   
>> Simon Riggs wrote:
>>     
>>> All of those responses have cooked up quite a few topics into one. Large
>>> databases might mean text warehouses, XML message stores, relational
>>> archives and fact-based business data warehouses.
>>>
>>> The main thing is that TB-sized databases are performance critical. So
>>> it all depends upon your workload really as to how well PostgreSQL, or
>>> another other RDBMS vendor can handle them.
>>>
>>>
>>> Anyway, my reason for replying to this thread is that I'm planning
>>> changes for PostgreSQL 8.4+ that will make allow us to get bigger and
>>> faster databases. If anybody has specific concerns then I'd like to hear
>>> them so I can consider those things in the planning stages
>>>       
>> it would be nice to do something with selects so we can recover a rowset 
>> on huge tables using a criteria with indexes without fall running a full 
>> scan.
>>
>> In my opinion, by definition, a huge database sooner or later will have 
>> tables far bigger than RAM available (same for their indexes). I think 
>> the queries need to be solved using indexes enough smart to be fast on disk.
>>     
>
> OK, I agree with this one. 
>
> I'd thought that index-only plans were only for OLTP, but now I see they
> can also make a big difference with DW queries. So I'm very interested
> in this area now.
>
>   
If that's true, then you want to get behind the work Gokulakannan 
Somasundaram 
(http://archives.postgresql.org/pgsql-hackers/2007-10/msg00220.php) has 
done with relation to thick indexes.  I would have thought that concept 
particularly useful in DW.  Only having to scan indexes on a number of 
join tables would be a huge win for some of these types of queries.

My tiny point of view would say that is a much better investment than 
setting up the proposed parameter.  I can see the use of the parameter 
though.  Most of the complaints about indexes having visibility is about 
update /delete contention.  I would expect in a DW that those things 
aren't in the critical path like they are in many other applications.  
Especially with partitioning and previous partitions not getting may 
updates, I would think there could be great benefit.  I would think that 
many of Pablo's requests up-thread would get significant performance 
benefit from this type of index.  But as I mentioned at the start, 
that's my tiny point of view and I certainly don't have the resources to 
direct what gets looked at for PostgreSQL.

Regards

Russell Smith