From: Steve Crawford <scrawford@pinpointresearch.com>
To: Navaneethan R <nava@gridlex.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Scaling 10 million records in PostgreSQL table
Date: Mon, 08 Oct 2012 13:09:59 -0700
Message-ID: <50733317.9080408@pinpointresearch.com> (raw)
In-Reply-To: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
References: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
On 10/08/2012 08:26 AM, Navaneethan R 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?
>
>
What version of PostgreSQL? You can use "select version();" and note
that 9.2 has index-only scans which can result in a substantial
performance boost for queries of this type.
What is the structure of your table? You can use "\d+
dealer_vehicle_details" in psql.
Have you tuned PostgreSQL in any way? If so, what?
Cheers,
Steve
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scrawford@pinpointresearch.com, nava@gridlex.com
Subject: Re: Scaling 10 million records in PostgreSQL table
In-Reply-To: <50733317.9080408@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox