Re: non-overlapping, consecutive partitions

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,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patriotism is when love of your own people comes first; nationalism,
> when hate for people other than your own comes first.
> - Charles de Gaulle