Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-29 Thread Hans-Jürgen Schönig
hello ...

yeah, this is fairly complicated.

greg:
can you send me how far you got?
i would be curious to see how you have attacked this issue.

i am still in the process of checking the codes.
we somehow have to find a solution for that. otherwise we are in slight trouble 
here.
it seems we have to solve it no matter what it takes.

many thanks,

hans


On Jul 26, 2010, at 1:14 AM, Robert Haas wrote:

 On Sun, Jul 25, 2010 at 6:40 PM, Greg Stark gsst...@mit.edu wrote:
 2010/7/25 Robert Haas robertmh...@gmail.com:
 2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at:
 
 On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote:
 
 I think the right way to approach this is to teach the planner about
 merge sorts.
 
 For what it's worth I think this is a belt-and-suspenders type of
 situation where we want two solutions which overlap somewhat.
 
 I would really like to have merge-append nodes because there are all
 sorts of plans where append nodes destroying the ordering of their
 inputs eliminates a lot of good plans. Those cases can be UNION ALL
 nodes, or partitions where there's no filter on the partition key at
 all.
 
 But for partitioned tables like the OPs the real solution would be
 to have more structured meta-data about the partitions that allows the
 planner to avoid needing the merge at all. It would also means the
 planner wouldn't need to look at every node; it could do a binary
 search or equivalent for the right partitions.
 
 Agreed on all points.
 
 Greg Stark had a patch to do this a while back called merge append,
 but it never got finished...
 
 I was basically in over my head with the planner. I don't understand
 how equivalent classes are used or should be used and didn't
 understand the code I was pointed at as being analogous. It's probably
 not so complicated as all that, but I never really wrapped my head
 around it and moved onto tasks I could make more progress on.
 
 Yeah, I don't fully understand those either.
 
 -- 
 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
 


--
Cybertec Schönig  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-25 Thread Martijn van Oosterhout
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   klep...@svana.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


signature.asc
Description: Digital signature


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-25 Thread PostgreSQL - Hans-Jürgen Schönig

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-25 Thread Robert Haas
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


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-25 Thread Greg Stark
2010/7/25 Robert Haas robertmh...@gmail.com:
 2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at:

 On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote:

 I think the right way to approach this is to teach the planner about
 merge sorts.

For what it's worth I think this is a belt-and-suspenders type of
situation where we want two solutions which overlap somewhat.

I would really like to have merge-append nodes because there are all
sorts of plans where append nodes destroying the ordering of their
inputs eliminates a lot of good plans. Those cases can be UNION ALL
nodes, or partitions where there's no filter on the partition key at
all.

But for partitioned tables like the OPs the real solution would be
to have more structured meta-data about the partitions that allows the
planner to avoid needing the merge at all. It would also means the
planner wouldn't need to look at every node; it could do a binary
search or equivalent for the right partitions.

 Greg Stark had a patch to do this a while back called merge append,
 but it never got finished...

I was basically in over my head with the planner. I don't understand
how equivalent classes are used or should be used and didn't
understand the code I was pointed at as being analogous. It's probably
not so complicated as all that, but I never really wrapped my head
around it and moved onto tasks I could make more progress on.

-- 
greg

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-25 Thread Robert Haas
On Sun, Jul 25, 2010 at 6:40 PM, Greg Stark gsst...@mit.edu wrote:
 2010/7/25 Robert Haas robertmh...@gmail.com:
 2010/7/25 PostgreSQL - Hans-Jürgen Schönig postg...@cybertec.at:

 On Jul 25, 2010, at 11:56 AM, Martijn van Oosterhout wrote:

 I think the right way to approach this is to teach the planner about
 merge sorts.

 For what it's worth I think this is a belt-and-suspenders type of
 situation where we want two solutions which overlap somewhat.

 I would really like to have merge-append nodes because there are all
 sorts of plans where append nodes destroying the ordering of their
 inputs eliminates a lot of good plans. Those cases can be UNION ALL
 nodes, or partitions where there's no filter on the partition key at
 all.

 But for partitioned tables like the OPs the real solution would be
 to have more structured meta-data about the partitions that allows the
 planner to avoid needing the merge at all. It would also means the
 planner wouldn't need to look at every node; it could do a binary
 search or equivalent for the right partitions.

Agreed on all points.

 Greg Stark had a patch to do this a while back called merge append,
 but it never got finished...

 I was basically in over my head with the planner. I don't understand
 how equivalent classes are used or should be used and didn't
 understand the code I was pointed at as being analogous. It's probably
 not so complicated as all that, but I never really wrapped my head
 around it and moved onto tasks I could make more progress on.

Yeah, I don't fully understand those either.

-- 
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


Re: [HACKERS] non-overlapping, consecutive partitions

2010-07-23 Thread Marko Tiikkaja

On 7/23/2010 11:04 PM, Hans-Jürgen Schönig wrote:

does anybody see a solution to this problem?
what are the main showstoppers to make something like this work?


I think we should absolutely make this work when we have a good 
partitioning implementation.


That said, I don't think it's wise to put a lot of effort into making 
this work with our current partitioning method when the partitioning 
patches are just around the corner.  The developer time should be 
directed at those patches instead.



Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers