Re: Separate the attribute physical order from logical order

Hi,

On Tue, Jun 28, 2022 at 08:38:56AM -0500, Justin Pryzby wrote:
> On Tue, Jun 28, 2022 at 04:32:30PM +0800, Julien Rouhaud wrote:
> > psql displays a table columns information using the logical order rather the
> > physical order, and if verbose emits an addition "Physical order" footer if the
> > logical layout is different from the physical one.
>
> FYI: the footer would work really poorly for us, since we use hundreds of
> columns and sometimes over 1000 (historically up to 1600).

Yeah :) As I mentioned originally at [1]: "I also changed psql to display the
column in logical position, and emit an extra line with the physical position
in the verbose mode, but that's a clearly poor design which would need a lot
more thoughts."

> I think it'd be
> better to show the physical position as an additional column, or a \d option to
> sort by physical attnum. (I'm not sure if it'd be useful for our case to see
> the extra columns, but at least it won't create a "footer" which is multiple
> pages long.

Yes, I was also thinking something like that could work. I just did it with
the extra footer for now because I needed a quick way to check in which order
my tables were supposed to be displayed / stored during development. As soon
as I get a clearer picture of what approach should be used I will clearly work
on this, and all other things that still need some care.

> Actually, I've sometimes wished for a "\d-" quiet mode which would
> show everything *except* the list of column names, or perhaps only show those
> columns which are referenced by the list of indexes/constraints/stats
> objects/etc).

I never had to work on crazy wide relations like that myself but I can easily
imagine how annoying it can get. No objection from me, although it would be
good to start a new thread to attract more attention and see what other are
thinking.

> BTW, since 2 years ago, when rewriting partitions to promote a column type, we
> recreate the parent table sorted by attlen, to minimize alignment overhead in
> new children. AFAICT your patch is about adding an logical column order, not
> about updating tables with a new physical order.

Indeed, the only thing it could do in such case is to allow you to create the
columns in an optimal order in the first place, without messing with the output.

But if the people who originally creates the table don't think about alignment
and things like that, there's still nothing that can be done with this feature.

That being said, in theory if such a feature existed, and if we also had a DDL
to allowed to specify a different logical order at creation time, it would be
easy to create a module that automatically reorder the columns before the table
is created to make sure that the columns are physically stored in an optimal
way.

[1] https://www.postgresql.org/message-id/20220623101155.3dljtwradu7eik6g@jrouhaud