Измерьте размер строки таблицы PostgreSQL.

У меня есть таблица PostgreSQL. select * очень медленный, тогда как select id приятный и быстрый. Я думаю, что размер строки очень большой, и для переноса требуется некоторое время, или это может быть какой-то другой фактор.

Мне нужны все поля (или почти все из них), поэтому выбор только подмножества не является быстрым решением. Выбор полей, которые я хочу, все еще медленный.

Вот моя схема таблицы минус имена:

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

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

Вопросы

  1. Есть ли что-нибудь в этом, что кричит «безумный неэффективен»?
  2. Есть ли способ измерить размер страницы в командной строке Postgres, чтобы помочь мне отладить это?
65 голосов | спросил Joe 7 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 07 Sep 2012 13:39:22 +0400 2012, 13:39:22

4 ответа


79

Q2: way to measure page size

PostgreSQL предоставляет ряд Функции размера объекта базы данных , вы можете использовать. Я собрал самые интересные из них в этом запросе и добавил некоторые Функции статистики доступа .

Это продемонстрирует, что различные методы измерения «размера строки» могут привести к очень различным результатам. Все зависит от того, что вы хотите точно измерить.

Замените public.tbl на ваше (необязательно, с помощью схемы) имя таблицы, чтобы получить компактный просмотр собранной статистики о размере ваших строк.

WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl  -- provide (qualified) table name here
   FROM   public.tbl t  -- ... and here
   )
, y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS what
     , unnest(val)                 AS "bytes/ct"
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / ct            AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;

Я снова упаковываю значения в массивы и unnest(), поэтому мне не нужно многократно выставлять вычисления для каждой отдельной строки.

Общая статистика count строк добавляется в конце с нетрадиционным SQL-foo, чтобы получить все в одном запросе. Вы можете перенести его в функцию plpgsql для повторного использования, указать имя таблицы в качестве параметра и использовать EXECUTE.

Результат:

что | bytes /ct | bytes_pretty | bytes_per_row
----------------------------------- + ---------- + --- ----------- + ---------------
 core_relation_size | 44138496 | 42 МБ | 91
 visibility_map | 0 | 0 байт | 0
 free_space_map | 32768 | 32 kB | 0
 table_size_incl_toast | 44179456 | 42 МБ | 91
 indexes_size | 33128448 | 32 МБ | 68
 total_size_incl_toast_and_indexes | 77307904 | 74 МБ | 159
 live_rows_in_text_representation | 29987360 | 29 МБ | 62
 ------------------------------ | | |
 row_count | 483424 | |
 live_tuples | 483424 | |
 dead_tuples | 2677 | |

Дополнительный модуль pgstattuple обеспечивает более полезные функции.

Обновление для Postgres 9.3 +

Мы могли бы использовать новую форму unnest() в стр. 9.4, используя несколько параметров для параллельной работы с массивами.
Но используя LATERAL и выражение VALUES , это может быть упрощены далее. Кроме того, некоторые другие улучшения:

SELECT l.what, l.nr AS "bytes/ct"
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / x.ct END          AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- same as 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t  -- provide table name *once*
   ) x
 , LATERAL (
   VALUES
      (true , 'core_relation_size'               , pg_relation_size(tbl))
    , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
    , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
    , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
    , (true , 'indexes_size'                     , pg_indexes_size(tbl))
    , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
    , (true , 'live_rows_in_text_representation' , txt_len)
    , (false, '------------------------------'   , NULL)
    , (false, 'row_count'                        , ct)
    , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
    , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, what, nr);

Тот же результат.

Q1: anything inefficient?

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

integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

Это экономит от 8 до 18 байт в строке. Я называю это "column tetris" . Подробности:

Также рассмотрим:

ответил Erwin Brandstetter 8 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowSat, 08 Sep 2012 20:08:53 +0400 2012, 20:08:53
22

Аппроксимация размера строки, включая содержание TOAST , легко получить, запросив длину представления TEXT всей строки:

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

Это приблизительное приближение к числу байтов, которые будут получены на стороне клиента при выполнении:

SELECT * FROM tablename WHERE primary_key=:value;

... предполагая, что вызывающий запрос запрашивает результаты в текстовом формате, что и делает большинство программ (двоичный формат возможен, но в большинстве случаев это не стоит проблем).

Та же самая методика может быть применена для поиска строк N «самых больших в тексте» tablename:

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;
ответил Daniel Vérité 7 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 07 Sep 2012 18:23:02 +0400 2012, 18:23:02
13

Есть несколько вещей, которые могут произойти. В общем, я сомневаюсь, что длина является проксимальной проблемой. Я подозреваю, что у вас есть проблема с длиной.

Вы говорите, что текстовые поля могут достигать нескольких k. Строка не может превышать 8k в основном хранилище, и вполне вероятно, что ваши большие текстовые поля были TOASTed или выведен из основного хранилища в расширенное хранилище в отдельных файлах. Это ускоряет работу вашего основного хранилища (так что выбор идентификатора на самом деле происходит быстрее, потому что меньше доступа к страницам на диске), но выберите *, становится медленнее, потому что существует более случайный ввод-вывод.

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

Первое, на что я бы посмотрел, - это выбрать меньшее количество строк и посмотреть, поможет ли это. Если это сработает, вы можете попробовать добавить больше ОЗУ на сервер, но я бы начал и посмотрел, где производительность начинает падать из-за изменений плана и промахов в кеше в первую очередь.

ответил Chris Travers 7 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 07 Sep 2012 16:47:34 +0400 2012, 16:47:34
3

Используя Функции размера объекта базы данных , упомянутых выше:

SELECT primary_key, pg_column_size(tablename.*) FROM tablename;

ответил WhiteFire Sondergaard 30 PMpThu, 30 Apr 2015 22:58:19 +030058Thursday 2015, 22:58:19

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132