Re: non-overlapping, consecutive partitions

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.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de