On Mon, Apr 2, 2007 at 2:44 AM, Phil Currier <[EMAIL PROTECTED]> wrote:
> On 4/1/07, Guillaume Smet <[EMAIL PROTECTED]> wrote:
>
> > Phil, did you make any progress with your patch? Your results seemed
> > very encouraging and your implementation interesting.
> > IIRC, the problem was that you wer
Phil Currier wrote:
I haven't done much with it since February, largely because my
available free time evaporated. But I do intend to get back to it
when I have a chance. But you're right, the storage position stuff
I've worked on is completely independent from display positions, and
certainly
On 4/1/07, Guillaume Smet <[EMAIL PROTECTED]> wrote:
Phil, did you make any progress with your patch? Your results seemed
very encouraging and your implementation interesting.
IIRC, the problem was that you weren't interested in working on the
"visual/mysqlish" column ordering. As the plan was t
On 2/20/07, Phil Currier <[EMAIL PROTECTED]> wrote:
Inspired by this thread [1], and in particular by the idea of storing
three numbers (permanent ID, on-disk storage position, display
position) for each column, I spent a little time messing around with a
prototype implementation of column storag
> >> I agree, I haven't thought of drop column :-( Drop column should
have
> >> relabeled attnum. Since it was not done then, my comments are
> >> probably moot.
> >
> > We can correct this problem now.
>
> How? If attnum is serving as both physical position and
> logical order, how can you m
> > I agree, I haven't thought of drop column :-( Drop column should
have
> > relabeled attnum.
> > Since it was not done then, my comments are probably moot.
>
> We can correct this problem now.
Do you mean fix it with the 3rd column in pg_attribute and use that,
or fix attnum ? :-)
Imho it
Kris Jurka escribió:
>
>
> On Thu, 22 Feb 2007, Alvaro Herrera wrote:
>
> >Zeugswetter Andreas ADI SD escribió:
> >>
> >>I agree, I haven't thought of drop column :-( Drop column should have
> >>relabeled attnum. Since it was not done then, my comments are probably
> >>moot.
> >
> >We can corr
On Thu, 22 Feb 2007, Alvaro Herrera wrote:
Zeugswetter Andreas ADI SD escribió:
I agree, I haven't thought of drop column :-( Drop column should have
relabeled attnum. Since it was not done then, my comments are probably
moot.
We can correct this problem now.
How? If attnum is servin
Zeugswetter Andreas ADI SD escribió:
>
> > > And I also see a lot of unhappiness from users of system tables
> > > when column numbers all over the system tables would not be
> > > logical column positions any more.
> >
> > Right now the fact that attnum presents the logical order but
> > not th
> > And I also see a lot of unhappiness from users of system tables when
> > column numbers all over the system tables would not be logical
column
> > positions any more.
>
> Right now the fact that attnum presents the logical order but
> not the logical position is a problem for the JDBC driv
On Thu, 22 Feb 2007, Zeugswetter Andreas ADI SD wrote:
And I also see a lot of unhappiness from users of system tables when
column numbers all over the system tables would not be logical column
positions any more.
Right now the fact that attnum presents the logical order but not the
logi
> > Yes, that was the idea (not oid but some number), and I am arguing
> > against it. Imho people are used to see the logical position in e.g.
> > pg_index
> >
>
> Which people are you talking about? In my commercial PG work
> I hardly ever look at a system table at all, and users
> shouldn't
Zeugswetter Andreas ADI SD wrote:
Yes, that was the idea (not oid but some number), and I am arguing
against it. Imho people are used to see the logical position in e.g.
pg_index
Which people are you talking about? In my commercial PG work I hardly
ever look at a system table at all, and
> > And I also see a lot of unhappiness from users of system tables when
> > column numbers all over the system tables would not be logical
column
> > positions any more.
>
> Are you arguing against the feature? Or against the suggested design?
Against the design.
> I should have thought (wit
Zeugswetter Andreas ADI SD wrote:
And I also see a lot of unhappiness from users of system tables
when column numbers all over the system tables would not be logical
column
positions any more.
Are you arguing against the feature? Or against the suggested design?
I should have thought (wi
> > In any case I think it's foolish not to tackle both issues at once.
> > We know we'd like to have both features and we know that
> all the same
> > bits of code need to be looked at to implement either.
>
> I guess I disagree with that sentiment. I don't think it's
> necessary to bundle t
On Thursday 22 February 2007 09:06, Phil Currier wrote:
> On 2/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> > Andrew Dunstan <[EMAIL PROTECTED]> writes:
> > > Alvaro Herrera wrote:
> > >> Right, I'm not advocating not doing that -- I'm just saying that the
> > >> first step to that could be decoupl
On 2/22/07, Tom Lane <[EMAIL PROTECTED]> wrote:
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> Right, I'm not advocating not doing that -- I'm just saying that the
>> first step to that could be decoupling physical position with attr id
>> :-) Logical column ordering (the o
On Wed, 2007-02-21 at 16:57 -0300, Alvaro Herrera wrote:
> Andrew Dunstan escribió:
> > Simon Riggs wrote:
> > >
> > >I agree with comments here about the multiple orderings being a horrible
> > >source of bugs, as well as lots of coding even to make it happen at all
> > >http://archives.postgresql
Andrew Dunstan <[EMAIL PROTECTED]> writes:
> Alvaro Herrera wrote:
>> Right, I'm not advocating not doing that -- I'm just saying that the
>> first step to that could be decoupling physical position with attr id
>> :-) Logical column ordering (the order in which SELECT * expands to)
>> seems to me
elein wrote:
The storage order is orthogonal to the display order. display order can be
handled
in attnum and the new storage order can be the new column.
If you review the earlier discussion you will see that it is proposed
(by Tom) to have 3 numbers (i.e. 2 new cols): an immutable id
On Wed, Feb 21, 2007 at 08:33:10PM +0100, Florian G. Pflug wrote:
> Simon Riggs wrote:
> >On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
> >>On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >>>I'd expect the system being able to reoder the columns to the most
> >>>efficient order pos
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
> Gregory Stark wrote:
>> "Andrew Dunstan" <[EMAIL PROTECTED]> writes:
>>
>>
>>> I would want to see this very carefully instrumented. Assuming we are
>>> putting
>>> all fixed size objects at the front, which seems like the best arrangement,
>>> th
Gregory Stark wrote:
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
I would want to see this very carefully instrumented. Assuming we are putting
all fixed size objects at the front, which seems like the best arrangement,
then the position of every fixed field and the fixed portion of the po
"Andrew Dunstan" <[EMAIL PROTECTED]> writes:
> I would want to see this very carefully instrumented. Assuming we are putting
> all fixed size objects at the front, which seems like the best arrangement,
> then the position of every fixed field and the fixed portion of the position
> of
> every va
Florian G. Pflug wrote:
BTW, this is a good case for why the storage order should - directly or
indirectly - be tweakable. You can either optimize for space, and _then_
for speed - which is what the OP did I think - or first for speed, and
then for space. If the dba cannot choose the strategy
Stephan Szabo wrote:
On Wed, 21 Feb 2007, Alvaro Herrera wrote:
Did I miss something in what you were trying to say? I assume you must
already know this.
I think so. What I was mentioning was that I was pretty sure that there
was a message with someone saying that they actually tried somethin
Stephan Szabo wrote:
What I was mentioning was that I was pretty sure that there
was a message with someone saying that they actually tried something that
did this and that they found left-most varchar access was slightly slower
after the reordering although general access was faster. I believ
On Wed, 21 Feb 2007, Alvaro Herrera wrote:
> Stephan Szabo escribi?:
> > On Wed, 21 Feb 2007, Martijn van Oosterhout wrote:
> >
> > > On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> > > > Well, for two reasons:
> > > >
> > > > 1) If you have a table with one very-frequently-accesse
Phil Currier escribió:
> On 2/21/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> >So yes, there would be a use case for specifying the physical column layout
> >when pg_migrator is doing the pg_dump/restore. But pg_migrator could
> >probably
> >just update the physical column numbers itself. It's n
On 2/21/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
So yes, there would be a use case for specifying the physical column layout
when pg_migrator is doing the pg_dump/restore. But pg_migrator could probably
just update the physical column numbers itself. It's not like updating system
catalog tabl
Alvaro Herrera wrote:
I haven't understood Alvaro to suggest not keeping 3 numbers.
Right, I'm not advocating not doing that -- I'm just saying that the
first step to that could be decoupling physical position with attr id
:-) Logical column ordering (the order in which SELECT * expands
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
> Why would a pg_migrator style upgrade use pg_dump at all? I assumed it
> would rather copy the verbatim data from the old to the new catalog,
> only changing it if the layout of the tables in pg_catalog actually changed.
The way pg_migrator works
Andrew Dunstan escribió:
> Simon Riggs wrote:
> >
> >I agree with comments here about the multiple orderings being a horrible
> >source of bugs, as well as lots of coding even to make it happen at all
> >http://archives.postgresql.org/pgsql-hackers/2006-12/msg00859.php
>
> I thought we were going
Stephan Szabo escribió:
> On Wed, 21 Feb 2007, Martijn van Oosterhout wrote:
>
> > On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> > > Well, for two reasons:
> > >
> > > 1) If you have a table with one very-frequently-accessed varchar()
> > > column and several not-frequently-acces
Simon Riggs wrote:
I agree with comments here about the multiple orderings being a horrible
source of bugs, as well as lots of coding even to make it happen at all
http://archives.postgresql.org/pgsql-hackers/2006-12/msg00859.php
I thought we were going with this later proposal of Tom's (o
Simon Riggs wrote:
On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When you create
On Wed, 21 Feb 2007, Martijn van Oosterhout wrote:
> On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> > Well, for two reasons:
> >
> > 1) If you have a table with one very-frequently-accessed varchar()
> > column and several not-frequently-accessed int columns, it might
> > actually
Phil Currier wrote:
On 2/21/07, Martijn van Oosterhout wrote:
> don't see any good way to perform an upgrade between PG versions
> without rewriting each table's data. Maybe most people aren't doing
> upgrades like this right now, but it seems like it will only become
> more common in the futu
On Wed, 2007-02-21 at 13:16 -0500, Andrew Dunstan wrote:
> Simon Riggs wrote:
> > On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
> >
> >> On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> >>
> >>> I'd expect the system being able to reoder the columns to the most
> >>> efficie
Simon Riggs wrote:
On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When yo
On 2/21/07, Martijn van Oosterhout wrote:
> don't see any good way to perform an upgrade between PG versions
> without rewriting each table's data. Maybe most people aren't doing
> upgrades like this right now, but it seems like it will only become
> more common in the future. In my opinion, t
On Wed, 2007-02-21 at 09:25 -0500, Phil Currier wrote:
> On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > I'd expect the system being able to reoder the columns to the most
> > efficient order possible (performance-wise and padding-saving-wise),
> > automatically. When you create a table,
On Wed, Feb 21, 2007 at 12:06:30PM -0500, Phil Currier wrote:
> Well, for two reasons:
>
> 1) If you have a table with one very-frequently-accessed varchar()
> column and several not-frequently-accessed int columns, it might
> actually make sense to put the varchar column first. The system won't
Andrew Dunstan wrote:
Florian G. Pflug wrote:
I think you'd want to have a flag per field that tell you if the user
has overridden the storage pos for that specific field. Otherwise,
the next time you have to chance to optimize the ordering, you might
throw away changes that the admin has done
Martijn van Oosterhout wrote:
On Wed, Feb 21, 2007 at 03:59:12PM +0100, Florian G. Pflug wrote:
I think you'd want to have a flag per field that tell you if the user
has overridden the storage pos for that specific field. Otherwise,
the next time you have to chance to optimize the ordering, you
On 2/21/07, Martijn van Oosterhout wrote:
On Wed, Feb 21, 2007 at 03:59:12PM +0100, Florian G. Pflug wrote:
> I think you'd want to have a flag per field that tell you if the user
> has overridden the storage pos for that specific field. Otherwise,
> the next time you have to chance to optimize
Florian G. Pflug wrote:
I think you'd want to have a flag per field that tell you if the user
has overridden the storage pos for that specific field. Otherwise,
the next time you have to chance to optimize the ordering, you might
throw away changes that the admin has done on purpose. The same ho
On Wed, Feb 21, 2007 at 03:59:12PM +0100, Florian G. Pflug wrote:
> I think you'd want to have a flag per field that tell you if the user
> has overridden the storage pos for that specific field. Otherwise,
> the next time you have to chance to optimize the ordering, you might
> throw away changes
Alvaro Herrera wrote:
> Bruce Momjian escribi?:
> > Phil Currier wrote:
> > > On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > > > I'd expect the system being able to reoder the columns to the most
> > > > efficient order possible (performance-wise and padding-saving-wise),
> > > > automat
Phil Currier wrote:
> On 2/21/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
> > Keep in mind we have a patch in process to reduce the varlena length and
> > reduce alignment requirements, so once that is in, reordering columns
> > will not be as important.
>
> Well, as I understand it, that patch i
On 2/21/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Keep in mind we have a patch in process to reduce the varlena length and
reduce alignment requirements, so once that is in, reordering columns
will not be as important.
Well, as I understand it, that patch isn't really addressing the same
pro
Bruce Momjian escribió:
> Phil Currier wrote:
> > On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > > I'd expect the system being able to reoder the columns to the most
> > > efficient order possible (performance-wise and padding-saving-wise),
> > > automatically. When you create a table,
Phil Currier wrote:
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When you create a table, sort the columns to the most
efficient order;
Phil Currier wrote:
> On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > I'd expect the system being able to reoder the columns to the most
> > efficient order possible (performance-wise and padding-saving-wise),
> > automatically. When you create a table, sort the columns to the most
> > e
On 2/21/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
I'd expect the system being able to reoder the columns to the most
efficient order possible (performance-wise and padding-saving-wise),
automatically. When you create a table, sort the columns to the most
efficient order; ALTER TABLE ADD COLU
Phil Currier escribió:
> Inspired by this thread [1], and in particular by the idea of storing
> three numbers (permanent ID, on-disk storage position, display
> position) for each column, I spent a little time messing around with a
> prototype implementation of column storage positions to see what
On Tuesday 20 February 2007 16:07, Phil Currier wrote:
> Another problem relates to upgrades. With tools like pg_migrator now
> on pgfoundry, people will eventually expect quick upgrades that don't
> require rewriting each table's data. Storage positions would cause a
> problem for every version
Just as my 2 cents to the proposed idea.
I want to demonstrate that the proposed idea is very relevant for the
performance.
I recently did an migration from PG 8.1 to PG 8.2. During that time I was
dumping the 2TB database with several very wide tables (having ~ 200
columns). And I saw that
Inspired by this thread [1], and in particular by the idea of storing
three numbers (permanent ID, on-disk storage position, display
position) for each column, I spent a little time messing around with a
prototype implementation of column storage positions to see what kind
of difference it would m
60 matches
Mail list logo
