Re: table partioning performance

On 1/7/07, Colin Taylor <colin.taylor@gmail.com> wrote:
> Hi there,  we've partioned a table (using 8.2) by day due to the 50TB of
> data (500k row size, 100G rows) we expect to store it in a year.
> Our performance on inserts and selects against the master table is
> disappointing, 10x slower (with ony 1 partition constraint) than we get  by
> going to the partioned table directly. Browsing the list I get the
> impression this just a case of too many partitions?  would be better off
> doing partitions of partitions ?
>
> Any other advice  or pointers to more information with dealing with these
> sorts of scales appreciated.

as others have stated, something is not set up correctly.  table
partitioning with constraint exclusion should be considerably faster
for situations were the planner can optimize for it (select queries
are case dependent,  but inserts are not).

also, I would like to speak for everybody else here and ask for as
much detail as possible about the hardware and software challenges you
are solving :-)  in particular, I am curious how you arrived at 500k
row size.

merlin