2010/7/25 PostgreSQL - Hans-Jürgen Schönig <postg...@cybertec.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
