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
>> 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...
The Enterprise Postgres Company
In response to
pgsql-hackers by date
|Next:||From: Robert Haas||Date: 2010-07-25 21:41:49|
|Subject: Re: psql \timing output supressed in quiet mode|
|Previous:||From: Robert Haas||Date: 2010-07-25 21:32:08|
|Subject: Re: including backend ID in relpath of temp rels - updated patch|