Re: Table with active and historical data

Hello,

I think, one good thing to do is partionning, you have already mentioned that in
your mail,
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

try to run also vaccuum command it might help in increasing the performance.

create a boolean flag i.e. active and create an index on it. I think this will
be faster than having index on date field

Regards

________________________________
From: Robert James <srobertjames(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Cc: srobertjames(at)gmail(dot)com
Sent: Thu, June 2, 2011 1:30:11 AM
Subject: [GENERAL] Table with active and historical data

I have a table with a little active data and a lot of historical data.
I'd like to be able to access the active data very quickly - quicker
than an index. Here are the details:

1. Table has about 1 million records
2. Has a column active_date - on a given date, only about 1% are
active. active_date is indexed and clustered on.
3. Many of my queries are WHERE active_date = today. Postgres uses
the index for these, but still lakes quite a lot of time. I repeat
these queries regularly.
4. I'd like to somehow presort or partition the data so that Postgres
doesn't have to do an index scan each time. I'm not sure how to do
this? Idea? I know it can be done with inheritance and triggers (
http://stackoverflow.com/questions/994882/what-is-a-good-way-to-horizontal-shard-in-postgresql

), but that method looks a little too complex for me. I'm looking for
something simple.
5. Finally, I should point out that I still do a large number of
queries on historical data as well.

What do you recommend? Ideas? Also: Why doesn't cluster on active_date
solve the problem? Specifically, if I run SELECT * FROM full_table
WHERE active_date = today, I get a cost of 3500. If I first select
those records into a new table, and then do SELECT * on the new table,
I get a cost of 64. Why is that? Why doesn't clustering pregroup
them?