Re: Table partitioning based on multiple criterias possible?

On Wed, 2007-09-12 at 09:42 +0100, Richard Huxton wrote:
> Ow Mun Heng wrote:
> > New to Pg and wondering the extent of PG's table partitioning
> > capability.
> >
> > I have a huge table > 18 million rows(growth rate ~8 million a week)
> > which I like to implement partitioning.
>
> OK
>
> > Initially the plan is to just partition it by date. eg: 1 partition per
> > month.
>
> Fair enough - do you do a lot of searching by date?

Yeah.. the products we usually query on are by dates. default is 1 to 2
weeks period. So, there will be overlaps eg: 3rd week of April to 1 week
of May.. which will hit 2 partitions

>
> > Now, I'm thinking if it's possible to also partition them by
> > product/items.
> >
> > is it possible to do multilevel partitioning sort of like
> >
> > if month = July then go to July Parition
> > if Product = Shampoo then go to shampoo partition
> > elif Product = Soap then go to soap partition
>
> Well, I suppose you could partition over (month,product) but you
> probably don't want to. Sounds fiddly to manage.

Yeah.. well current schema w/ everything in 1 table and the way things
are ordered, normalised design etc, is causing PG to calc stats wrongly
and i get a 1 to 500x ratio between plan and actual analysed rows which
is causing queries to run > 2 hours w/o returning results. (this was
tested w/ on tables w/ only ~3 million rows and joining another table w/
another 3 million data - one to many relationship, I think, is causing
the mis-estimates.)
>
> Have you verified that performance will be a problem without this?