Re: non-overlapping, consecutive partitions - Mailing list pgsql-hackers

From PostgreSQL - Hans-Jürgen Schönig
Subject Re: non-overlapping, consecutive partitions
Date
Msg-id 3FF38A84-45D0-434B-9510-3CD4F28FA617@cybertec.at
Whole thread Raw
In response to Re: non-overlapping, consecutive partitions  (Martijn van Oosterhout <kleptog@svana.org>)
Responses Re: non-overlapping, consecutive partitions
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: psql \timing output supressed in quiet mode
Next
From: Kris Jurka
Date:
Subject: Re: [JDBC] Trouble with COPY IN