From: | Alex Stapleton <alexs(at)advfn(dot)com> |
---|---|
To: | Edoceo Lists <lists(at)edoceo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Large Table Performance |
Date: | 2005-10-22 01:26:14 |
Message-ID: | DDA6C042-2DAA-49AA-8A56-C6A2DDA6F0C4@advfn.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 22 Oct 2005, at 01:25, Edoceo Lists wrote:
> List,
> I've got a problem where I need to make a table that is going to
> grow by an average of 230,000 records per day. There are only 20
> columns in the table, mostly char and integer. It's FK'd in two
> places to another table for import/export transaction id's and I
> have a serial primary key and an index on a date column for when I
> need to search (every search is done inside a date range). I
> thought it would be OK but after a few weeks of operation I have
> more than five million records in there. Some queries take more
> than five minutes to complete and I'm sad about that. How can I
> make this faster? I could munge dates into integers if their
> faster, I'm OK with that. What can I tweak in the configuration
> file to speed things up? What about some drastic schema change
> that more experience would have shown me? I cannot show the full
> schema but it's like this:
>
> -- My import/export data information table
> ie_data (id serial primary key, date date, [12 other columns here])
>
> big_transaction_table(id serial primary key, import_id int w/FK,
> export_id int w/FK, date date, [20 other necessary transaction
> detail columns])
>
> So when I say
> select x,y,z from big_transaction_table where date>='10/2/2005' and
> date<='10/4/2005' and transaction_status in (1,2,3) order by date;
> it takes five+ minutes.
>
> TIA for any suggestions.
What hardware are you on? What query plans (output from explain) do
your queries give you? What PG version?
We do about 100,000 rows a minute (300 MB+) a day so I suspect your
queries are doing full table scans or something. Of course we don't
use any FKs so I suppose they could be biting you.
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2005-10-22 01:41:04 | Re: Large Table Performance |
Previous Message | Johan Wehtje | 2005-10-22 00:26:23 | Re: PgInstallerfor 8.1 beta 3 missing Postgis? |