From: Larry Rosenman <ler@lerctr.org>
To: pgsql-performance@postgresql.org
Subject: Re: Scaling 10 million records in PostgreSQL table
Date: Mon, 08 Oct 2012 14:53:48 -0500
Message-ID: <0465ecd0abdd8c64e254b08ae327f566@webmail.lerctr.org> (raw)
In-Reply-To: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
References: <b2d0ada9-f13a-4269-b378-1ceaf2451190@googlegroups.com>
On 2012-10-08 10:26, 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 indexes do you have on your table?
I'll bet none.
What does an explain select count(*) from dealer_vehicle_details where
modified_on
between '2012-10-01' and '2012-10-08' and dealer_id=270001;
show?
I have a 380Million row table, with LOTS of indexing, and we perform
very well.
Without indexes, the query had to sequential scan all 10 million rows.
That's going to be bad on ANY database.
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: ler@lerctr.org
Subject: Re: Scaling 10 million records in PostgreSQL table
In-Reply-To: <0465ecd0abdd8c64e254b08ae327f566@webmail.lerctr.org>
* 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