Re: about partitioning

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