Message-ID: <50733741.30609@ringerc.id.au>
Date: Tue, 09 Oct 2012 04:27:45 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Navaneethan R <nava@gridlex.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Scaling 10 million records in PostgreSQL table
References: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
In-Reply-To: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 10/08/2012 11:26 PM, Navaneethan R wrote:
> Hi all,
>
>        I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance.

EC2 usually means "My I/O performance is terrible" and "medium instance" 
means "I don't have enough RAM for caching to make up for my terrible 
I/O" at the database sizes you're talking.

Anything that hits most of the database is likely to perform pretty 
poorly on something like EC2. It might be worth considering one of the 
high memory or high I/O instances, but unfortunately they only come in 
"really big and really expensive".

If you already have appropriate indexes and have used `explain analyze` 
to verify that the query isn't doing anything slow and expensive, it's 
possible the easiest way to improve performance is to set up async 
replication or log shipping to a local hot standby on real physical 
hardware, then do the query there.

--
Craig Ringer