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@xxxxxxxxxxxxxxxx ______________________________________________ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
