Re: nested partitioning

On 09/18/2013 12:20 PM, Shaun Thomas wrote:
> On 09/18/2013 11:05 AM, "Gabriel E. Sánchez Martínez" wrote:
>
>> Or would it have to check every child partition, as follows?
>>
>> #
>> Check partition 2000-01-01, 2000-01-02, ... , 2013-09-18. 5010 checks.
>> All days but 2012-01-01 are excluded. 5009 partitions excluded with
>> 5010 constraint exclusion checks.
>
> My guess based on the fact the planner has no concept of ranges aside
> from their width, is that this is the approach it'll likely take. You
> can't really teach it that your particular inheritance tree is range
> constrained per level, so it has to check every range.
>

In that case maybe this could be considered as a performance improvement
for future versions of PG? If I am not mistaken check constraints are
inherited from parent table to child table, making it feasible for the
query planner to prune entire branches at once by checking the
inheritance structure. I know that query performance should be very
good with partitions for each month and indexes on the timestamp column,
but I can think of many applications where data is extracted in daily
batches and it is more natural and convenient for DB maintenance to have
a partition per day. In big data applications, it may be feasible to
fit a day's worth of data in RAM, but not a whole month; in such cases
there could be performance gains from tree constraint exclusion.

Thanks for your insight.