Re: Column lookup in a row performance

David,

> Tradeoffs? As I mentioned. Surely the offset per tuple actually must
> be stored somewhere, and that storage is not free.

What offset do you mean?

Consider an example. Let's define a table as follows:
create table test(
name1 varchar(255),
name2 varchar(255)
);

And add one tuple
insert into test values('john', 'doe');

In postgres it is stored like
| null_bitmap | 'john' length | 'john' bytes | 'doe' length | 'doe' bytes |

And to find 'doe' we must went throuh 'john'.

In innodb it is stored like
| 'john' length | 'doe' length | null_bitmap | 'john' bytes | 'doe' bytes |

And to find 'doe' we can calculates an offset (from columns data
position) as a sum of lengths of all preceeding columns and jump
directly to it. In the example an offset is equal to 'john' length.
Storage space requirements looks very similar, does not they?

ср, 27 мар. 2019 г. в 05:17, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>:
>
> On Wed, 27 Mar 2019 at 04:16, Павлухин Иван <vololo100(at)gmail(dot)com> wrote:
> > It seems that an innodb layout is better at least for reading. So, it
> > is still unclear for me why postgresql does not employ similar layout
> > if it can give significant benefits.
>
> Tradeoffs? As I mentioned. Surely the offset per tuple actually must
> be stored somewhere, and that storage is not free.
>
> Another way it could be done... There have been a few projects in the
> past to allow the logical column order in a table to differ from the
> physical column order. None of these got very far IIRC due to concerns
> about code existing that used the incorrect attnum (e.g the logical
> instead of physical one), but it's possible that something like this
> would help, assuming that internally we tried to decide what the
> "best" order was during CREATE TABLE. Perhaps roughly fixed width and
> non-null columns come first then the remaining in some other order.
> However, even if we had that then there still seems to be a problem
> with the tuple containing NULLs. We only have a single bit to mark if
> the tuple contains any NULLs. Probably we'd need to know the attnum of
> the first NULL, or maybe we could get away with improving TupleDesc so
> it cached the first attnum without a NOT NULL constraint so that we
> knew we could use the cached offset before that attnum even if the
> tuple has NULLs later in the tuple. However, that puts a lot of
> dependency on the NOT NULL constraint never being wrong.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services

--
Best regards,
Ivan Pavlukhin