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?