Skip site navigation (1) Skip section navigation (2)

Re: Decreasing BLKSZ

From: Jim Nasby <jim(at)nasby(dot)net>
To: Marc Morin <marc(at)sandvine(dot)com>
Cc: "Bucky Jordan" <bjordan(at)lumeta(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Markus Schaber" <schabi(at)logix-tt(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Decreasing BLKSZ
Date: 2006-09-27 03:42:16
Message-ID: F52319C5-EFE2-4A33-A0C5-693CF3EB13B3@nasby.net (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)nasby(dot)net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)



In response to

pgsql-performance by date

Next:From: Tim TrumanDate: 2006-09-27 06:40:11
Subject: Forcing the use of particular execution plans
Previous:From: Jim NasbyDate: 2006-09-27 03:28:41
Subject: Re: PostgreSQL and sql-bench

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group