Re: non-overlapping, consecutive partitions

2010/7/25 PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>:
>
> On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote:
>
>> On Fri, Jul 23, 2010 at 10:04:00PM +0200, Hans-Jürgen Schönig wrote:
>>>      create table foo ( x date );
>>>      create table foo_2010 () INHERITS (foo)
>>>      create table foo_2009 () INHERITS (foo)
>>>      create table foo_2008 () INHERITS (foo)
>>>
>>> now we add constraints to make sure that data is only in 2008, 2009 and 2010.
>>> we assume that everything is indexed:
>>>
>>> SELECT * FROM foo ORDER BY bar  will now demand an ugly sort for this data.
>>> this is not an option if you need more than a handful of rows ...
>>
>> I think the right way to approach this is to teach the planner about
>> merge sorts. This is, if the planner has path to foo_* all ordered by
>> the same key (because they have the same indexes) then it has a path to
>> the UNION of those tables simply by merging the results of those paths.
>>
>> This would be fairly straight forward to implement I think, you may
>> even be able to reuse the merge sort in the normal sort machinery.
>> (You'll need to watch out for UNION vs UNION ALL.)
>>
>> The real advantage of this approach is that you no longer have to prove
>> anything about the constraints or various datatypes and it is more
>> general. Say you have partitioned by start_date but you want to sort by
>> end_date, simple index scanning won't work while a merge sort will work
>> beautifully.
>>
>> You're also not limited to how the partitioning machinery will
>> eventually work.
>>
>> Hope this helps,
>
>
> i think this is excellent input.
> i will do some research going into that direction.

Greg Stark had a patch to do this a while back called merge append,
but it never got finished...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company