> AFAIU the OP is trying to give the cache a chance of
> doing some useful
> work by partitioning by time so it's going to be forced to
> go to disk
> less.
Exactly
> have you
> considered a couple of
> "levels" to your hierarchy. Maybe bi-hourly (~15
> million records?)
> within the current day and move them over into a "day"
> table at night
I was going for the partitioned-index approach because
it would avoid re-copying the data over another table.
My idea was:
1) create partial indexes on today's table
2) at night, create a whole index (not partial) on yesterday's
table
3) drop the partial indexes on yesterday's table
But this doesn't work, because partial indexes aren't
"appended" the way partitioned tables are... that is, if I have
one index covering half table, and another covering the other
half, if I query the data over the "intersection" I'll always get
a plain table scan, where I would expect the planner to do
an append of the result of 2 index scans...
Would it be something that could be added to the TODO list?
It doesn't look that different from what table partitioning/pruning
does....
Thank you everybody for your replies anyway!