Skip site navigation (1) Skip section navigation (2)

Re: non-overlapping, consecutive partitions

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
Cc: Martijn van Oosterhout <kleptog(at)svana(dot)org>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Boszormenyi Zoltan <zb(at)cybertec(dot)at>
Subject: Re: non-overlapping, consecutive partitions
Date: 2010-07-25 21:32:57
Message-ID: AANLkTinE=Dcqv0tPY9yyG8Mg9fVq1eEBy=EDSsBovRy6@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-07-25 21:41:49
Subject: Re: psql \timing output supressed in quiet mode
Previous:From: Robert HaasDate: 2010-07-25 21:32:08
Subject: Re: including backend ID in relpath of temp rels - updated patch

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group