Re: about partitioning - Mailing list pgsql-general

From Simon Riggs
Subject Re: about partitioning
Date
Msg-id 1221547082.3913.1656.camel@ebony.2ndQuadrant
Whole thread Raw
In response to Re: about partitioning  (Robert Treat <robert@omniti.com>)
List pgsql-general
On Mon, 2008-09-15 at 10:05 -0400, Robert Treat wrote:
> Blazej wrote:
> > (1) Are you testing INSERT/UPDATE performance when is only one rule
> > script for all partition tables?
>
> I've tested a number of different scenarios; I guess I'd say as a
> general rule, the more rules you have, likely the worse your performance
> will get; but really I would use triggers anyway

I would try to load data directly into a partition if possible. If this
is possible, you get to skip worrying about all that stuff completely
and performance is much better. You can't COPY into a table using RULEs,
and if you've got enough data to use partitioning then you will want to
use COPY to load data. Choosing a partition key that is optimal for
loading and queries will help you.

> > (2) Are you using "SET constraint_exclusion = on;"?
> >
>
> yes, otherwise there isnt much point :-)

I think Blazej means "in the postgresql.conf".

Probably yes, but not in all cases. If you have queries that access the
partitioned table and yet don't benefit from constraint exclusion, then
you will be adding time and CPU for no benefit. If you see it CPU bound
then its worth looking at in more detail.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


pgsql-general by date:

Previous
From: "Pavel Stehule"
Date:
Subject: Re: HOWTO: select * from array_type
Next
From: lighthouse.software@gmail.com
Date:
Subject: DbLink Compilation on Solaris 10