X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 6B27F9DC84B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 13 Dec 2005 19:54:11 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 69761-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 13 Dec 2005 19:54:12 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.193])
	by postgresql.org (Postfix) with ESMTP id CB0149DC813
	for <pgsql-performance@postgresql.org>;
	Tue, 13 Dec 2005 19:54:07 -0400 (AST)
Received: by zproxy.gmail.com with SMTP id 13so916279nzn
	for <pgsql-performance@postgresql.org>;
	Tue, 13 Dec 2005 15:54:10 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=ptFOLyAY00LSDQltMfdKrKZtN+vU/QJ+6NcAQo/nxFXzYweofj9XcwcHBdmzk0p+S/A/GFoc2b+Rye1i8tKw9wfGZL+PBLiTVCNRRhS47lpwoega9J7uRVZjlup4qpQem8GmzlOayiKVnuAn9gbUCmvLwQ8qDtnTtjoy88FNicQ=
Received: by 10.65.75.20 with SMTP id c20mr142259qbl;
	Tue, 13 Dec 2005 15:54:08 -0800 (PST)
Received: by 10.64.53.8 with HTTP; Tue, 13 Dec 2005 15:54:07 -0800 (PST)
Message-ID: <bd0eabd0512131554v451a99f2u4ac64e403e9b6b49@mail.gmail.com>
Date: Wed, 14 Dec 2005 12:54:08 +1300
From: Mike C <smith.not.western@gmail.com>
To: Simon Riggs <simon@2ndquadrant.com>
Subject: Re: Table Partitions / Partial Indexes
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1134515825.27873.122.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_10439_17780420.1134518048005"
References: <bd0eabd0512111807q6b54115q2477c7c2a37f1c1a@mail.gmail.com>
	<1134515825.27873.122.camel@localhost.localdomain>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.107,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.108
X-Spam-Level: 
X-Archive-Number: 200512/323
X-Sequence-Number: 16144

------=_Part_10439_17780420.1134518048005
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 12/14/05, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> Maybe not for queries, but if you use a date range then you never need
> to run a DELETE and never need to VACUUM.
>
> You could split the data into two-day chunks.


That's an interesting idea, thanks.

> Am I using a horrid method for partitioning the data? (% 10)
>
> No, but what benefit do you think it provides. I'm not sure I see...


I was trying to get both the indexes to be smaller without loosing
selectivity, and make any table scans/index scans faster from having to rea=
d
less data.

> Should there be that big of an improvement for multiple tables given
> > that all the data is still stored on the same filesystem?
>
> You could store partitions in separate tablespaces/filesystems.
>

Ideally that's what I would do, but to make the most of that I would have t=
o
have a dedicated RAID setup for each partition right? (Which is a bit price=
y
for the budget).

Cheers,

Mike

------=_Part_10439_17780420.1134518048005
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

On 12/14/05, <b class=3D"gmail_sendername">Simon Riggs</b> &lt;<a href=3D"m=
ailto:simon@2ndquadrant.com">simon@2ndquadrant.com</a>&gt; wrote:<div><span=
 class=3D"gmail_quote"></span><blockquote class=3D"gmail_quote" style=3D"bo=
rder-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding=
-left: 1ex;">
Maybe not for queries, but if you use a date range then you never need<br>t=
o run a DELETE and never need to VACUUM.<br><br>You could split the data in=
to two-day chunks.</blockquote><div><br>
That's an interesting idea, thanks.<br>
</div><br><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid=
 rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">&gt; Am=
 I using a horrid method for partitioning the data? (% 10)<br><br>No, but w=
hat benefit do you think it provides. I'm not sure I see...
</blockquote><div><br>
I was trying to get both the indexes to be smaller without loosing
selectivity, and make any table scans/index scans faster from having to
read less data.<br>
<br>
</div><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb=
(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">&gt; Should=
 there be that big of an improvement for multiple tables given<br>&gt; that=
 all the data is still stored on the same filesystem?
<br><br>You could store partitions in separate tablespaces/filesystems.<br>=
</blockquote></div><br>
Ideally that's what I would do, but to make the most of that I would
have to have a dedicated RAID setup for each partition right? (Which is
a bit pricey for the budget).<br>
<br>
Cheers,<br>
<br>
Mike<br>

------=_Part_10439_17780420.1134518048005--