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.
/djb