Column lookup in a row performance

From: Павлухин Иван <vololo100(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Column lookup in a row performance
Date: 2019-03-22 06:13:37
Message-ID: CAOykqKc8Uoi3NKVfd5DpTmUzD4rJBWG9Gjo3pr7eaUGLtrstvw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi PostgreSQL Community,

I am learning deeply how tuples are organized and column values are
accessed in different databases. As far as undertood postgres does not
store all column positions in a tuple (e.g. in header or footer). In
contrast MySQL InnoDB stores column lengths in a record header [1].
From the first glance it seems that a postgres format can have a
significant performance penalty when accessing a single column which
is located after multiple variable-length columns because searching a
column value position in a row requires multiple jumps. And in InnoDB
a position of a particular column can be found right after reading a
header.

I found several related threads in pgsql-hackers archives [2,3]
describing significant performance wins in a prototype.

Does anyone know why the format is still the same? Perhaps InnoDB and
similar formats are not so good, are they?

Please respond if you have the clue!

[1] https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html#innodb-row-format-compact
[2] https://www.postgresql.org/message-id/flat/c58979e50702201307w64b12892uf8dfc3d8bf117ec0%40mail.gmail.com
[3] https://www.postgresql.org/message-id/flat/87irj16umm.fsf%40enterprisedb.com

--
Best regards,
Ivan Pavlukhin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrus 2019-03-22 09:00:49 How to check is connection encrypted
Previous Message Adrian Klaver 2019-03-22 04:10:24 Re: Materialized view breaks pg_restore