Re: Column lookup in a row performance

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