Re: [HACKERS] Simple Column reordering
Josh Berkus wrote: For my part, I continue to the interested in this proposal and would like to see some performance benchmarks on it. If there is enough performance gain, I think it would be possible to implement a "logical" order which was different from the "physical" order. Such a feature would also allow us to give the MySQLites the ability to re-order columns they keep asking for. That is indeed what has been proposed (see earlier discussion re Tom's 3 numbers idea). Simon's proposal would not have done that, however, but would simply have mutated the (single physical+logical) column order. Anyway, I think we're all (or mostly all) on the same page now. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Simple Column reordering
Bruce, > True, but usually I don't see the breakage. What concerned me is you > saw some of the breakage, but still went ahead with the proposal. That's completely unfair, Bruce. This is a *discussion list*, and hackers are free to propose and discuss even far-out improbable ideas in the hopes that someone else on the list can make them work. -hackers is not a clearinghouse for already-perfected work; that's -patches. For my part, I continue to the interested in this proposal and would like to see some performance benchmarks on it. If there is enough performance gain, I think it would be possible to implement a "logical" order which was different from the "physical" order. Such a feature would also allow us to give the MySQLites the ability to re-order columns they keep asking for. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: > > > wondered why that proposal had been overlooked, so I started a separate > > > thread to ensure that the idea was discussed. That seems very similar to > > > many of your own posts. > > > > True, but usually I don't see the breakage. > > Sorry, I just meant you summarise ideas that others have made, not that > your proposals are broken. My proposals are often broken. > > What concerned me is you > > saw some of the breakage, but still went ahead with the proposal. > > I have never and will never propose something I know to be broken. That > shouldn't need to be said, but I've had to say that more than once > recently for some reason. Why would you even think that the author of > PITR would harbour some hidden disrespect for server integrity, or > somebody who overhauled the standards compliance documentation, with > Troels, has no respect for standards? I was merely trying to focus us on making sure we stay on track. I know you share the same goals. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Simple Column reordering
"Simon Riggs" <[EMAIL PROTECTED]> writes: > The order of the columns is *arbitrary* in relational theory; SQL is very far from being relational theory... regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Simple Column reordering
On Mon, 2007-02-26 at 13:02 -0500, Bruce Momjian wrote: > Simon Riggs wrote: > > On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > > > > > I realized this proposal has been withdrawn, but the fact the proposal > > > even illicited comments exploring it requires me to comment. > > > > > > Folks, how can we entertain ideas that would break SELECT * and > > > no-column-list INSERTs for a small performance boost? If there was no > > > other way to get the performance boost, and the features was rarely > > > used, we might consider such a change, but neither is true in this case. > > > > > > My point is that this proposal is so far away from our acceptable > > > criteria that I am worried about how people are analyzing proposals. > > > > When suggested, it wasn't clear to me that it did break anything, > > otherwise I wouldn't have written it up. I read Alvaro's post and > > You mentioned in your own original posting that it broke SELECT * and > COPY. I saw that there was an effect, not breakage; I didn't use that word. I specifically highlighted that there would be a difference because it was an area of possible contention. The order of the columns is *arbitrary* in relational theory; the ordering needs to match to allow DDL to match other SQL that presumes an ordering. Changing the order at CREATE TABLE time seemed acceptable and would be so in many cases, since most applications follow sensible guidelines about not using SELECT * etc. But SQL Standard breakage is not acceptable. My mistake was mis-reading the Standard, which regrettably is not the easiest manual to read, but no excuse. The functionality could still be usefully implemented in a client tool, which was where the discussion left. > > wondered why that proposal had been overlooked, so I started a separate > > thread to ensure that the idea was discussed. That seems very similar to > > many of your own posts. > > True, but usually I don't see the breakage. Sorry, I just meant you summarise ideas that others have made, not that your proposals are broken. > What concerned me is you > saw some of the breakage, but still went ahead with the proposal. I have never and will never propose something I know to be broken. That shouldn't need to be said, but I've had to say that more than once recently for some reason. Why would you even think that the author of PITR would harbour some hidden disrespect for server integrity, or somebody who overhauled the standards compliance documentation, with Troels, has no respect for standards? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: > On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > > > I realized this proposal has been withdrawn, but the fact the proposal > > even illicited comments exploring it requires me to comment. > > > > Folks, how can we entertain ideas that would break SELECT * and > > no-column-list INSERTs for a small performance boost? If there was no > > other way to get the performance boost, and the features was rarely > > used, we might consider such a change, but neither is true in this case. > > > > My point is that this proposal is so far away from our acceptable > > criteria that I am worried about how people are analyzing proposals. > > When suggested, it wasn't clear to me that it did break anything, > otherwise I wouldn't have written it up. I read Alvaro's post and You mentioned in your own original posting that it broke SELECT * and COPY. > wondered why that proposal had been overlooked, so I started a separate > thread to ensure that the idea was discussed. That seems very similar to > many of your own posts. True, but usually I don't see the breakage. What concerned me is you saw some of the breakage, but still went ahead with the proposal. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Simple Column reordering
On Mon, 2007-02-26 at 11:20 -0500, Bruce Momjian wrote: > I realized this proposal has been withdrawn, but the fact the proposal > even illicited comments exploring it requires me to comment. > > Folks, how can we entertain ideas that would break SELECT * and > no-column-list INSERTs for a small performance boost? If there was no > other way to get the performance boost, and the features was rarely > used, we might consider such a change, but neither is true in this case. > > My point is that this proposal is so far away from our acceptable > criteria that I am worried about how people are analyzing proposals. When suggested, it wasn't clear to me that it did break anything, otherwise I wouldn't have written it up. I read Alvaro's post and wondered why that proposal had been overlooked, so I started a separate thread to ensure that the idea was discussed. That seems very similar to many of your own posts. In the morning light, holding a coffee, its obviously broken and it is clear that I misunderstood what was being proposed. Bottom line is I initiate lots of ideas, not all of which become projects, but many become TODO items. Sometimes I see things others do not, sometimes I make mistakes; sometimes it takes a while for my ideas to be understood and accepted. I don't hold mistakes against anyone, and we all make them. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Simple Column reordering
> In Simon's defense, I think we need to feel free to brainstorm a bit, > and propose things that might seem odd. There are plenty of cool heads > around to shoot down bad ideas, but we'll only make progress by > cherry-picking the good ideas. If one out of ten of my ideas is useful I > think I'm doing really well. The easiest way to spot genius is to talk to the craziest person in the room. Joshua D. Drake > > cheers > > andrew > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Simple Column reordering
Andrew Dunstan wrote: > Bruce Momjian wrote: > > > > Folks, how can we entertain ideas that would break SELECT * and > > no-column-list INSERTs for a small performance boost? If there was no > > other way to get the performance boost, and the features was rarely > > used, we might consider such a change, but neither is true in this case. > > > > My point is that this proposal is so far away from our acceptable > > criteria that I am worried about how people are analyzing proposals. > > > > > > In Simon's defense, I think we need to feel free to brainstorm a bit, > and propose things that might seem odd. There are plenty of cool heads > around to shoot down bad ideas, but we'll only make progress by > cherry-picking the good ideas. If one out of ten of my ideas is useful I > think I'm doing really well. Agreed. I just wanted to make sure we still have the same criteria. The fact the proposal was explored just got me worried. I will go back to not worrying. ;-) -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simple Column reordering
Bruce Momjian wrote: Folks, how can we entertain ideas that would break SELECT * and no-column-list INSERTs for a small performance boost? If there was no other way to get the performance boost, and the features was rarely used, we might consider such a change, but neither is true in this case. My point is that this proposal is so far away from our acceptable criteria that I am worried about how people are analyzing proposals. In Simon's defense, I think we need to feel free to brainstorm a bit, and propose things that might seem odd. There are plenty of cool heads around to shoot down bad ideas, but we'll only make progress by cherry-picking the good ideas. If one out of ten of my ideas is useful I think I'm doing really well. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Simple Column reordering
I realized this proposal has been withdrawn, but the fact the proposal even illicited comments exploring it requires me to comment. Folks, how can we entertain ideas that would break SELECT * and no-column-list INSERTs for a small performance boost? If there was no other way to get the performance boost, and the features was rarely used, we might consider such a change, but neither is true in this case. My point is that this proposal is so far away from our acceptable criteria that I am worried about how people are analyzing proposals. --- Simon Riggs wrote: > Column storage position is the subject of many long threads in recent > times. Solutions proposed for this have been both fairly complex and > long enough that nothing seems likely to happen for 8.3. If I'm wrong, > then of course this proposal would be superceded. > > I propose that at CREATE TABLE time, the column ordering is re-ordered > so that the table columns are packed more efficiently. This would be a > physical re-ordering, so that SELECT * and COPY without explicit column > definitions would differ from the original CREATE TABLE statement. > > This would be an optional feature, off by default, controlled by a > USERSET GUC > optimize_column_order = off (default) | on > > When the full column ordering proposal is implemented, > optimize_column_ordering would be set to default to on. The feature > would be supported for at least one more release after this to allow bug > analysis. > > The proposed ordering would be: > 1. All fixed length columns, arranged so that alignment is efficient > 2. All variable length columns > > All column ordering would stay as close as possible to original order > > No changes would be made apart from at CREATE TABLE time. > > The ordering would be repeatable, so that the order would not change on > repeated dump/restore of a table with no changes. > > -- > Simon Riggs > EnterpriseDB http://www.enterprisedb.com > > > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
Bruce Momjian wrote: Lukas Kahwe Smith wrote: Simon Riggs wrote: If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, nor does CREATE TABLE enforce the ordering to be the same as it has specified, AFAICS. Please correct me and I will withdraw. Practical issues seem far stronger drivers than standards issues here, which is why the parameter would default=off. I did not follow the entire thread. I just wanted to point out that IIRC MS SQL Server (and maybe also Sybase) do automatically optimize the internal order of how columns are stored to move fixed length (which also means non NULLable for these two servers) columns to the left. Maybe this will serve as a reference point (not necessarily for standards compliance of course). And that optimized ordering shows up with SELECT *? no .. like i said its just the internal order .. I think I learned this piece of information from reading "SQL performance tuning" by Peter and Trudy .. dont have the book here right now to give a page number. regards, Lukas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simple Column reordering
Lukas Kahwe Smith wrote: > Simon Riggs wrote: > > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. Please correct me and I will > > withdraw. Practical issues seem far stronger drivers than standards > > issues here, which is why the parameter would default=off. > > I did not follow the entire thread. I just wanted to point out that IIRC > MS SQL Server (and maybe also Sybase) do automatically optimize the > internal order of how columns are stored to move fixed length (which > also means non NULLable for these two servers) columns to the left. > Maybe this will serve as a reference point (not necessarily for > standards compliance of course). And that optimized ordering shows up with SELECT *? -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Simple Column reordering
First, it would absolutely be best if we just got the full blown patch into 8.3 and were done with it. I don't think anyone's arguing against that... it's a question of what we can do if that can't happen (and it does sound like the patch lost it's maintainer when the direction changed towards doing both physical and logical ordering code at the same time). On Fri, Feb 23, 2007 at 01:04:14PM -0300, Alvaro Herrera wrote: > Jim C. Nasby wrote: > > On Fri, Feb 23, 2007 at 02:09:55PM +, Simon Riggs wrote: > > > > If you really want an interim solution, what about a builtin function > > > > that would explicitly mutate the definition and table contents (if any) > > > > along the lines you want? (assuming that's lots less work than just > > > > doing the whole thing right to start with). Or even one which just > > > > *displayed* the optimal order might be sufficient assistance to DBAs > > > > who > > > > want to take advantage of this. > > > > > > I think the only interim solution now is to put functionality into > > > PgAdmin et al to optimize the column order. > > > > Well, if it comes to that it would be good to have pgAdmin et all driven > > by logic in the database, so that people using psql can benefit as well. > > Perhaps a function that is passed an existing table and re-creates it in > > optimal order (if it's empty...). Or at least spits out a CREATE TABLE > > statement for you that's in optimal order. > > That's just working around the fact that the engine is not smart enough > to do the right thing (semi-) automatically. We don't support that kind > of operation, just like we don't support optimizer hints. Except it's kind of the opposite... in this case, the database actually knows better about what fields have what alignment, etc. At least if users can get what the database says will be the best order they can use that should they choose to. I also don't see why we should restrict that information to users of pgAdmin or other 3rd party tools and not support those that just use psql. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Simple Column reordering
Jim C. Nasby wrote: > On Fri, Feb 23, 2007 at 02:09:55PM +, Simon Riggs wrote: > > > If you really want an interim solution, what about a builtin function > > > that would explicitly mutate the definition and table contents (if any) > > > along the lines you want? (assuming that's lots less work than just > > > doing the whole thing right to start with). Or even one which just > > > *displayed* the optimal order might be sufficient assistance to DBAs who > > > want to take advantage of this. > > > > I think the only interim solution now is to put functionality into > > PgAdmin et al to optimize the column order. > > Well, if it comes to that it would be good to have pgAdmin et all driven > by logic in the database, so that people using psql can benefit as well. > Perhaps a function that is passed an existing table and re-creates it in > optimal order (if it's empty...). Or at least spits out a CREATE TABLE > statement for you that's in optimal order. That's just working around the fact that the engine is not smart enough to do the right thing (semi-) automatically. We don't support that kind of operation, just like we don't support optimizer hints. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Simple Column reordering
On Fri, Feb 23, 2007 at 02:09:55PM +, Simon Riggs wrote: > > If you really want an interim solution, what about a builtin function > > that would explicitly mutate the definition and table contents (if any) > > along the lines you want? (assuming that's lots less work than just > > doing the whole thing right to start with). Or even one which just > > *displayed* the optimal order might be sufficient assistance to DBAs who > > want to take advantage of this. > > I think the only interim solution now is to put functionality into > PgAdmin et al to optimize the column order. Well, if it comes to that it would be good to have pgAdmin et all driven by logic in the database, so that people using psql can benefit as well. Perhaps a function that is passed an existing table and re-creates it in optimal order (if it's empty...). Or at least spits out a CREATE TABLE statement for you that's in optimal order. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Simple Column reordering
"Simon Riggs" <[EMAIL PROTECTED]> writes: > If this is standards-breaking as you say, I would withdraw immediately. > I checked the SQL standard and could not see how this would do so. The > standard states SELECT * would return columns in order; it doesn't say > what that order should be, nor does CREATE TABLE enforce the ordering to > be the same as it has specified, AFAICS. SQL92 7.9 defines the meaning of SELECT * as b) Otherwise, the "*" is equivalent to a sequence in which each is a that references a column of T and each column of T is referenced exactly once. The columns are ref- erenced in the ascending sequence of their ordinal position within T. 11.3 says 2) The degree of the table being created is initially set to 0; the General Rules of Subclause 11.4, "" specify the degree of the table being created during the definition of columns in that table. and 11.4 says 4) The degree of the table T being defined in the containing or or altered by the containing is increased by 1. 5) A column descriptor is created that describes the column being defined. The ordinal position included in the column descriptor is equal to the degree of T. Now, I will grant you that it doesn't actually say anywhere that the column definitions in CREATE TABLE must be processed left to right, but if they meant this behavior to be implementation-dependent they would have said so. Given the number of places in the spec in which semantics are directly dependent on ordinal position, I cannot think that that is intended --- for example, the behavior of becomes completely undefined if column ordinal positions aren't fixed. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
On Fri, 2007-02-23 at 07:52 -0500, Andrew Dunstan wrote: > I really don't think that we can accept under any circumstances a > situation where something ... breaks: Yes, I've accepted that, in response to Peter earlier today. > If you really want an interim solution, what about a builtin function > that would explicitly mutate the definition and table contents (if any) > along the lines you want? (assuming that's lots less work than just > doing the whole thing right to start with). Or even one which just > *displayed* the optimal order might be sufficient assistance to DBAs who > want to take advantage of this. I think the only interim solution now is to put functionality into PgAdmin et al to optimize the column order. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: On Fri, 2007-02-23 at 11:25 +0100, Peter Eisentraut wrote: Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs: If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, b) Otherwise, the “*” is equivalent to a sequence in which each is a column reference that references a column of T and each column of T is referenced exactly once. The columns are referenced in the ascending sequence of their ordinal position within T. Which begs the question: what is their ordinal position? If we change the ordinal position at CREATE TABLE time then the SELECT * would still work per standard. That's quite a stretch. Surely "their ordinal position" can't mean "their ordinal position as arbitrarily determined at CREATE TABLE time by the implementation". I really don't think that we can accept under any circumstances a situation where something as simple as this breaks: create table foo (x text, y int); insert into foo values ('qwerty',1); Physical storage optimization must not have any SQL level visibility or consequences, IMNSHO, regardless of what we do about providing mutable display order. If you really want an interim solution, what about a builtin function that would explicitly mutate the definition and table contents (if any) along the lines you want? (assuming that's lots less work than just doing the whole thing right to start with). Or even one which just *displayed* the optimal order might be sufficient assistance to DBAs who want to take advantage of this. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
Am Freitag, 23. Februar 2007 12:25 schrieb Simon Riggs: > My reading was that this was about constraints on columns, not the > columns themselves, when that phrase was taken in context. I take it you > think that reading was wrong? I see nothing there that speaks of constraints. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Simple Column reordering
On Fri, 2007-02-23 at 11:25 +0100, Peter Eisentraut wrote: > Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs: > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, > > b) Otherwise, the “*” is equivalent to a > sequence in which each is a column reference that > references a column of T and each column of T is referenced exactly once. The > columns are referenced in the ascending sequence of their ordinal position > within T. Which begs the question: what is their ordinal position? If we change the ordinal position at CREATE TABLE time then the SELECT * would still work per standard. So the next point is the important one: > > nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. > > b) The column descriptors of every column of T, according to the Syntax Rules > and General Rules of Subclause 11.4, “”, applied to the > s contained in TEL, in the order in which they were > specified. I read both of these phrases prior to posting the original suggestion. My reading was that this was about constraints on columns, not the columns themselves, when that phrase was taken in context. I take it you think that reading was wrong? I'm not a lawyer, so I'll not argue too close to the edge. Suggestion withdrawn. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simple Column reordering
Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs: > If this is standards-breaking as you say, I would withdraw immediately. > I checked the SQL standard and could not see how this would do so. The > standard states SELECT * would return columns in order; it doesn't say > what that order should be, b) Otherwise, the “*” is equivalent to a sequence in which each is a column reference that references a column of T and each column of T is referenced exactly once. The columns are referenced in the ascending sequence of their ordinal position within T. > nor does CREATE TABLE enforce the ordering to > be the same as it has specified, AFAICS. b) The column descriptors of every column of T, according to the Syntax Rules and General Rules of Subclause 11.4, “”, applied to the s contained in TEL, in the order in which they were specified. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
On 2/23/07, Simon Riggs <[EMAIL PROTECTED]> wrote: I had read that Phil had declined to work on it further; I hope he changes his mind on that. IIRC he just said he wasn't interested to work on the visible ordering part (as in MySQL) and I don't think it's a problem as even if it's related it's a different problem. -- Guillaume ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: >>> I propose that at CREATE TABLE time, the column ordering is re-ordered >>> so that the table columns are packed more efficiently. This would be a >>> physical re-ordering, so that SELECT * and COPY without explicit column >>> definitions would differ from the original CREATE TABLE statement. How about INSERTs without column names? Wouldn't that also cease to work? In particular, 'pg_dump --format=plain --inserts' would produce unusable output unless --attribute-inserts is also given, right? Yours, Laurenz Albe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Simple Column reordering
On Fri, 2007-02-23 at 09:46 +0100, Guillaume Smet wrote: > Hi Simon, > > On 2/23/07, Simon Riggs <[EMAIL PROTECTED]> wrote: > > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say > > what that order should be, nor does CREATE TABLE enforce the ordering to > > be the same as it has specified, AFAICS. Please correct me and I will > > withdraw. > > Phil Currier seems to have a preliminary proposition so I think we > should wait for his patch. That is exactly what I've said also, so it is good we agree. I had read that Phil had declined to work on it further; I hope he changes his mind on that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Simple Column reordering
> > If this is standards-breaking as you say, I would withdraw immediately. > > I checked the SQL standard and could not see how this would do so. The > > standard states SELECT * would return columns in order; it doesn't say Imho the create table order is implied. What other order would they mean with "in order" ? > > what that order should be, nor does CREATE TABLE enforce the ordering > > to be the same as it has specified, AFAICS. Please correct me and I > > will withdraw. Practical issues seem far stronger drivers than > > standards issues here, which is why the parameter would default=off. > > I did not follow the entire thread. I just wanted to point > out that IIRC MS SQL Server (and maybe also Sybase) do > automatically optimize the internal order of how columns are > stored to move fixed length (which also means non NULLable > for these two servers) columns to the left. > Maybe this will serve as a reference point (not necessarily > for standards compliance of course). If you state that, it is imho also important to note that this is not externally visible. select * is not altered. I think we can have this based on Phil's patch. Simon would probably be willing to extend it to choose the best physical order during create table time. And the first step would be done. The next step could then be to "fix" drop column and add "add column before" eighter using a 3rd column or using the existing 2. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Simple Column reordering
Hi Simon, On 2/23/07, Simon Riggs <[EMAIL PROTECTED]> wrote: If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, nor does CREATE TABLE enforce the ordering to be the same as it has specified, AFAICS. Please correct me and I will withdraw. Phil Currier seems to have a preliminary proposition so I think we should wait for his patch. AFAICS in what he did, the physical position is not tied to the column ordering and it's better IMHO. It doesn't seem very intrusive and it doesn't change the "visible" behavior of PostgreSQL. Personnaly, I really need my column ordering. When I create my table, I put the columns in a logical order and I need they stay as I created them (for \d for example - on a large table I make semantic groups so I can find the field names more easily). And I think a lot of people do it as well so I'm not sure people would use a GUC which changes that much their habits. -- Guillaume ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Simple Column reordering
> I don't understand the reluctance to implementing all of it. > The most serious objection I've seen, from Andreas IIRC, is > that it would make drivers' lives more difficult; but really, > drivers have to cope with dropped columns today which is a Yes, I already said, that my objection is probably moot in face of drop column. > pain, and ISTM this proposal (not this one here, but the > three-column proposal) would make that a bit simpler. Do all of them support dropped columns correctly, yet ? Andreas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, nor does CREATE TABLE enforce the ordering to be the same as it has specified, AFAICS. Please correct me and I will withdraw. Practical issues seem far stronger drivers than standards issues here, which is why the parameter would default=off. I did not follow the entire thread. I just wanted to point out that IIRC MS SQL Server (and maybe also Sybase) do automatically optimize the internal order of how columns are stored to move fixed length (which also means non NULLable for these two servers) columns to the left. Maybe this will serve as a reference point (not necessarily for standards compliance of course). regards, Lukas ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Simple Column reordering
On Thu, 2007-02-22 at 20:07 -0600, Andrew Dunstan wrote: > Simon Riggs wrote: > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > so that the table columns are packed more efficiently. This would be a > > physical re-ordering, so that SELECT * and COPY without explicit column > > definitions would differ from the original CREATE TABLE statement. > > > > This would be an optional feature, off by default, controlled by a > > USERSET GUC > > optimize_column_order = off (default) | on > > > > > Umm, you want a GUC setting to enable standards-breaking behaviour and > that will be obsolete when we do column ordering right, which is not > likely to be more than one release away, and could even still happen in > this coming release? If this is standards-breaking as you say, I would withdraw immediately. I checked the SQL standard and could not see how this would do so. The standard states SELECT * would return columns in order; it doesn't say what that order should be, nor does CREATE TABLE enforce the ordering to be the same as it has specified, AFAICS. Please correct me and I will withdraw. Practical issues seem far stronger drivers than standards issues here, which is why the parameter would default=off. If the full implementation exists and works, I would welcome it. This proposal is really aimed at what we do if that doesn't occur; we must wait to see if it will. I see that many users would want to get something sooner rather than later. That isn't a commercial perspective, I see that as a PostgreSQL advocacy perspective. I also see that we are forcing change into the on-disk format of heaps in this release. If we defer this to another release then we would be effectively changing the on-disk format again in next release. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Simple Column reordering
On Thu, 2007-02-22 at 23:49 -0300, Alvaro Herrera wrote: > Andrew Dunstan wrote: > > Simon Riggs wrote: > > > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > > so that the table columns are packed more efficiently. This would be a > > > physical re-ordering, so that SELECT * and COPY without explicit column > > > definitions would differ from the original CREATE TABLE statement. > > > > > > This would be an optional feature, off by default, controlled by a > > > USERSET GUC > > > optimize_column_order = off (default) | on > > > > Umm, you want a GUC setting to enable standards-breaking behaviour and > > that will be obsolete when we do column ordering right, which is not > > likely to be more than one release away, and could even still happen in > > this coming release? > > Given that we already seem to have a patch implementing a complete > solution, or part thereof, this would seem a rather shortsighted > proposal. Why not develop the whole thing and be done with it? That would be my preference, but if it doesn't happen, I wanted to have a clear secondary proposal documented. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Simple Column reordering
"Alvaro Herrera" <[EMAIL PROTECTED]> writes: > Given that we already seem to have a patch implementing a complete > solution we do? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Simple Column reordering
Andrew Dunstan wrote: > Simon Riggs wrote: > > > > I propose that at CREATE TABLE time, the column ordering is re-ordered > > so that the table columns are packed more efficiently. This would be a > > physical re-ordering, so that SELECT * and COPY without explicit column > > definitions would differ from the original CREATE TABLE statement. > > > > This would be an optional feature, off by default, controlled by a > > USERSET GUC > > optimize_column_order = off (default) | on > > Umm, you want a GUC setting to enable standards-breaking behaviour and > that will be obsolete when we do column ordering right, which is not > likely to be more than one release away, and could even still happen in > this coming release? Given that we already seem to have a patch implementing a complete solution, or part thereof, this would seem a rather shortsighted proposal. Why not develop the whole thing and be done with it? I don't understand the reluctance to implementing all of it. The most serious objection I've seen, from Andreas IIRC, is that it would make drivers' lives more difficult; but really, drivers have to cope with dropped columns today which is a pain, and ISTM this proposal (not this one here, but the three-column proposal) would make that a bit simpler. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Simple Column reordering
Simon Riggs wrote: > > I propose that at CREATE TABLE time, the column ordering is re-ordered > so that the table columns are packed more efficiently. This would be a > physical re-ordering, so that SELECT * and COPY without explicit column > definitions would differ from the original CREATE TABLE statement. > > This would be an optional feature, off by default, controlled by a > USERSET GUC > optimize_column_order = off (default) | on > Umm, you want a GUC setting to enable standards-breaking behaviour and that will be obsolete when we do column ordering right, which is not likely to be more than one release away, and could even still happen in this coming release? I hope I haven't misunderstood. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Simple Column reordering
Column storage position is the subject of many long threads in recent times. Solutions proposed for this have been both fairly complex and long enough that nothing seems likely to happen for 8.3. If I'm wrong, then of course this proposal would be superceded. I propose that at CREATE TABLE time, the column ordering is re-ordered so that the table columns are packed more efficiently. This would be a physical re-ordering, so that SELECT * and COPY without explicit column definitions would differ from the original CREATE TABLE statement. This would be an optional feature, off by default, controlled by a USERSET GUC optimize_column_order = off (default) | on When the full column ordering proposal is implemented, optimize_column_ordering would be set to default to on. The feature would be supported for at least one more release after this to allow bug analysis. The proposed ordering would be: 1. All fixed length columns, arranged so that alignment is efficient 2. All variable length columns All column ordering would stay as close as possible to original order No changes would be made apart from at CREATE TABLE time. The ordering would be repeatable, so that the order would not change on repeated dump/restore of a table with no changes. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly