Horton

Re: Scaling 10 million records in PostgreSQL table

Merlin Moncure <mmoncure@gmail.com>

View thread View on postgr.es
From: Merlin Moncure <mmoncure@gmail.com>
To: Navaneethan R <nava@gridlex.com>
Cc: pgsql-performance@postgresql.org
Date: 2012-10-08T19:50:16Z
Lists: pgsql-performance 
On Mon, Oct 8, 2012 at 10:26 AM, Navaneethan R <nava@gridlex.com> wrote:
> Hi all,
>
>       I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. I need to access the last week data from the table.
> It takes huge time to process the simple query.So, i throws time out exception error.
>
> query is :
>      select count(*) from dealer_vehicle_details where modified_on between '2012-10-01' and '2012-10-08' and dealer_id=270001;
>
> After a lot of time it responds 1184 as count
>
> what are the ways i have to follow to increase the performance of this query?
>
> The insertion also going parallel since the daily realtime updation.
>
> what could be the reason exactly for this lacking performace?

can you send explain analyze?  also table structure?

merlin