Re: nested partitioning

On 09/18/2013 07:45 AM, "Gabriel E. Sánchez Martínez" wrote:

> Now to the questions. Would constraint exclusion work in a tree
> fashion to prune tables from a query plan?

Constraint exclusion works by checking the constraints themselves. So if
your constraints are based on date, they will always work as expected.
Assuming they're generated, you could easily check down to the day level
by >= desired day, and < next day.

With that in mind, if you queried a specific day, the query would remove
every partition but the relevant day (or range of days).

> One can see that with a decade of data stored, there would be
> approximately 3652 partitions. The documentation discourages having
> this many partitions.

And you shouldn't. In our partitions, we try to keep it around one
partition per month. I've recently written a library that eschews
triggers in favor of a nightly movement job to redistribute data, and it
can handle partition granularity on a sliding scale. The default is one
month, but my tests show it works properly down to 1 day. Still, the
lowest I'd use it is probably 1 week. That would still drastically
reduce your amount of estimated partitions.

However you decide to do it, don't optimize prematurely. Monthly
partitions are enough for us and our 130GB table with 250M rows, and
that's for a single year.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email