Message-ID: <50018BFC.1080701@ringerc.id.au>
Date: Sat, 14 Jul 2012 23:10:52 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Robert Klemme <shortcutter@googlemail.com>
CC: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: Any tool/script available which can be used to measure
	scalability of an application's database.
References: 
 <OF43A6984C.A368D80E-ON65257A37.00211A41-65257A37.002DEE2E@tcs.com>
	<CAM9pMnMcsU0qWb_a2xPHdJ5oobscptE5votqY2T95GnRw7c=pA@mail.gmail.com>
	<CANVt3iY4ipHJG3+j=c6iO7GDRBT4m-hKAMD=mkf9P8k6q8S_Uw@mail.gmail.com>
	<50013279.4090407@ringerc.id.au>
	<CANVt3iYn_VUvxPDs67smQbCUsRuGejwHeHWm_itgGbVCxeUgKA@mail.gmail.com>
	<CAM9pMnPTF-MQCdM7BE45gWS_zMTGX1jNev0EGNXgSjCdMLJizw@mail.gmail.com>
In-Reply-To: 
 <CAM9pMnPTF-MQCdM7BE45gWS_zMTGX1jNev0EGNXgSjCdMLJizw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 07/14/2012 08:17 PM, Robert Klemme wrote:
> On Sat, Jul 14, 2012 at 11:50 AM, B Sreejith <bsreejithin@gmail.com> wrote:
>> Dear All,
>> Thanks alot for all the invaluable comments.
> Additionally to Craig's excellent advice to measurements there's
> something else you can do: with the knowledge of the queries your
> application fires against the database you can evaluate your schema
> and index definitions.  While there is no guarantee that your
> application will scale well if all indexes are present
Don't forget that sometimes it's better to DROP an index that isn't used 
much, or that only helps occasional queries that aren't time-sensitive. 
Every index has a cost to maintain - it slows down your inserts and 
updates and it competes for disk cache with things that might be more 
beneficial.
> b) do not have indexes nor no indexes which
> support the queries your application does against these tables which
> will result in full table scans.
A full table scan is not inherently a bad thing, even for a huge table. 
Sometimes you just need to examine every row, and the fastest way to do 
that is without a doubt a full table scan.

Remember, a full table scan won't tend to push everything out of 
shared_buffers, so it can also avoid competition for cache.

(If anyone ever wants concurrent scans badly enough to implement them, 
full table scans with effective_io_concurrency > 1 will become a *lot* 
faster for some types of query).

--
Craig Ringer