Влияет ли порядок столбцов в таблице Postgres на производительность?

Влияет ли в Postgres порядок столбцов в операторе CREATE TABLE на производительность? Рассмотрим следующие два случая:

CREATE TABLE foo (
  a      TEXT, 
  B      VARCHAR(512),
  pkey   INTEGER PRIMARY KEY,
  bar_fk INTEGER REFERENCES bar(pkey),
  C      bytea
);

vs.

CREATE TABLE foo2 (
  pkey   INTEGER PRIMARY KEY,
  bar_fk INTEGER REFERENCES bar(pkey),
  B      VARCHAR(512),      
  a      TEXT, 
  C      bytea
);

Будет ли производительность foo2 лучше, чем foo из-за лучшего выравнивания байтов для столбцов? Когда Postgres выполняет CREATE TABLE, следует ли указанному порядку столбцов или реорганизует столбцы в оптимальном порядке для выравнивания байтов или производительности?


postgresql storage performance database-design postgresql-performance
person ams    schedule 26.09.2012    source источник

Ответы (2)


arrow_upward
26
arrow_downward

Вопрос 1

Будет ли производительность foo2 лучше, чем foo из-за лучшего выравнивания байтов для столбцов?

Да, порядок столбцов может иметь небольшое влияние на производительность. Выравнивание типов является более важным фактором, поскольку оно влияет на размер диска. Вы можете минимизировать размер хранилища (поиграть в «колоночный тетрис») и втиснуть больше строк на страницу данных, что является наиболее важным фактором для скорости.

Обычно заморачиваться не стоит. В крайнем примере, как в этом связанном ответе, вы получаете существенную разницу:

Введите сведения о выравнивании:

Другой фактор заключается в том, что извлечение значений столбца происходит немного быстрее, если сначала у вас есть столбцы фиксированного размера. Я цитирую руководство здесь:

Чтобы прочитать данные, вам нужно проверить каждый атрибут по очереди. Сначала проверьте, является ли поле NULL в соответствии с нулевым растровым изображением. Если это так, перейдите к следующему. Затем убедитесь, что у вас правильное выравнивание. Если поле имеет фиксированную ширину, то все байты просто размещаются. Если это поле переменной длины (attlen = -1), то это немного сложнее. Все типы данных переменной длины имеют общую структуру заголовка struct varlena, которая включает общую длину хранимого значения и некоторые биты флага.

Существует открытый элемент TODO, позволяющий изменять порядок позиций столбцов в Postgres Wiki, отчасти для этих целей. причины.

вопрос 2

Когда Postgres выполняет CREATE TABLE, следует ли указанному порядку столбцов или реорганизует столбцы в оптимальном порядке для выравнивания байтов или производительности?

Столбцы хранятся в заданном порядке, система не пытается оптимизировать.

Я не вижу никакой связи порядка столбцов с таблицами TOAST, как и другие ответ, кажется, подразумевает.

person Erwin Brandstetter    schedule 26.09.2012
comment
Проект PostgreSQL хочет добавить автоматическую оптимизацию физического порядка. wiki.postgresql.org/wiki/ - person Paul Draper; 21.09.2018
comment
проголосовал! как текст и varchar работают в этом выравнивании - person PirateApp; 02.12.2019
comment
@PirateApp: text и varchar используют выравнивание по символам, то есть без выравнивания. Добавил ссылку на подробности. - person Erwin Brandstetter; 03.12.2019

arrow_upward
4
arrow_downward

Насколько я понимаю, PostgreSQL придерживается порядка ввода столбцов при сохранении записей. Вопрос о том, влияет ли это на производительность, спорный. PostgreSQL хранит все данные таблицы на страницах размером 8 КБ каждая. 8 КБ по умолчанию, но его можно изменить во время компиляции.

Каждая строка в таблице будет занимать место на странице. Поскольку определение вашей таблицы содержит переменные столбцы, страница может состоять из переменного количества записей. Что вы хотите сделать, так это убедиться, что вы можете разместить как можно больше записей на одной странице. Вот почему вы заметите снижение производительности, когда таблица имеет огромное количество столбцов или размеры столбцов огромны.

При этом объявление varchar(8192) не означает, что страница будет заполнена одной записью, но объявление CHAR(8192) будет использовать целую страницу независимо от количества данных в столбце.

Есть еще одна вещь, которую следует учитывать при объявлении типов TOASTable, таких как столбцы TEXT. Это столбцы, размер которых может превышать максимальный размер страницы. Таблица со столбцами TOASTable будет иметь связанную таблицу TOAST для хранения данных, и в таблице хранится только указатель на данные. Это может повлиять на производительность, но его можно улучшить с помощью правильных индексов в столбцах TOASTable.

В заключение я должен сказать, что порядок столбцов не играет большой роли в производительности таблицы. Большинство запросов используют индексы, которые хранятся отдельно для извлечения записей, поэтому порядок столбцов инвертируется. Это сводится к тому, сколько страниц нужно прочитать, чтобы получить данные.

person Imraan    schedule 26.09.2012
comment
Кажется, вы подразумеваете связь между порядком столбцов и таблицами TOAST, которую я не вижу. Часть о TOAST просто не по теме. Кроме того, ваш абзац об индексах неверен. В большинстве случаев Postgres должен считывать фактические строки из таблицы в дополнение к сканированию индексов (новый сканирование только по индексу (за исключением функции Postgres 9.2, которая работает только в оптимальных условиях). Индексы очень помогают, но не полностью исключают влияние порядка столбцов. - person Erwin Brandstetter; 26.09.2012
comment
Я не говорю, что существует связь между порядком столбцов и таблицами TOAST. Столбцы с поддержкой TOAST — это еще одна вещь, на которую следует обратить внимание при рассмотрении производительности таблицы. После сканирования индекса все зависит от того, сколько страниц необходимо прочитать для загрузки данных. Поэтому порядок столбцов не имеет значения. - person Imraan; 27.09.2012