On Sep 26, 2006, at 5:36 PM, Marc Morin wrote:
> 1- partitions loaded without indexes on them.. And build index "when
> partition is full". Slow to drill down into incomplete partitions.
> 2- paritions with index as loaded. Slow, on insert (problem
> mentioned)
> but good to drill down....
How big are your partitions? The number of rows in your active
partition will determine how large your indexes are (and probably
more importantly, how many levels there are), which will definitely
affect your timing. So, you might have better luck with a smaller
partition size.
I'd definitely try someone else's suggestion of making the PK
logtime, key (assuming that you need to enforce uniqueness) and
having an extra index on just key. If you don't need to enforce
uniqueness, just have one index on key and one on logtime. Or if your
partitions are small enough, don't even create the logtime index
until the partition isn't being inserted into anymore.
If the number of key values is pretty fixed, it'd be an interesting
experiment to try partitioning on that, perhaps even with one key per
partition (which would allow you to drop the key from the tables
entirely, ie:
CREATE TABLE stats_1 (logtime PRIMARY KEY, stat1, stat2, stat3);
CREATE TABLE stats_2 ...
CREATE VIEW stats AS
SELECT 1 AS key, * FROM stats_1
UNION ALL SELECT 2, * FROM stats_2
...
I wouldn't put too much work into that as no real effort's been
expended to optimize for that case (especially the resulting monster
UNION ALL), but you might get lucky.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)