F.21. Hydra Оптимизированный Строково-Колонковый (ORC)#

F.21. Hydra Оптимизированный Строково-Колонковый (ORC)
F.21. Hydra Оптимизированный Строково-Колонковый (ORC)
Назад НаверхПредметный указатель F. Дополнительные поставляемые модули и расширенияНачало Далее

F.21. Hydra Оптимизированный Строково-Колонковый (ORC) #

F.21.1. О Hydra Columnar
F.21.2. Введение
F.21.3. Установка в столбцовом формате
F.21.4. Основные принципы колоночного хранения
F.21.5. Использование функциональности метода доступа к таблице
F.21.6. Использование колоночного формата
F.21.7. Микробенчмарк производительности
F.21.8. Общие рекомендации
F.21.9. Работа с Time Series-данными

F.21.1. О Hydra Columnar #

Версия: 1.1.2

Страница проекта www.hydra.so

GitHub

Авторские права © Hydra.

F.21.2. Введение #

В традиционных аналитических системах (OLAP) размер и объем данных со временем увеличиваются, что потенциально может привести к различным проблемам с производительностью. Метод колоночного хранения обеспечивает значительные преимущества в случаях, когда системы управления базами данных (СУБД) используются для аналитических задач, где основным способом загрузки данных является пакетная загрузка.

С данным форматом вы можете сжимать таблицы для значительного снижения потребления ресурсов и достижения высокой производительности. При этом нет необходимости переключения на распределенную массивно параллельную СУБД. Колоночная структура ORC в хранилище Columnar обеспечивает высокую производительность за счет чтения соответствующих данных с диска и их более эффективного сжатия.

Примечание

Метод колоночного хранения не полностью заменяет распределенные массивно параллельные СУБД, но исключает их использование в некоторых случаях. Решение о переходе на массивно параллельные СУБД зависит от конкретных объемов данных и требований к рабочей нагрузке. Хотя пороговое значение варьируется, обычно оно находится в пределах десятков терабайт.

F.21.3. Установка в столбцовом формате #

Метод хранения в виде столбцов устанавливается как расширение. Чтобы добавить Columnar в вашу локальную базу данных Tantor SE, подключитесь с помощью psql и выполните: 

CREATE EXTENSION pg_columnar;

Теперь вы можете создавать и использовать колоночные таблицы в вашей базе данных.

F.21.4. Основные принципы колоночного хранения #

Стандартный метод хранения данных heap в Tantor SE хорошо работает для нагрузки OLTP:

  • Поддержка операций UPDATE/DELETE,

  • Эффективный поиск одиночных кортежей

Таблицы в столбцовом формате наиболее подходят для аналитических или складских нагрузок, где следующие преимущества будут полезны по сравнению с методом кучи:

  • Сжатие

  • Не читаются ненужные столбцы

  • Эффективное использование VACUUM.

F.21.5. Использование функциональности метода доступа к таблице #

Преимущества хранилища Columnar по сравнению с cstore_fdw:

  • Хранение в столбцах основано на API метода доступа к таблицам. Этот API позволяет выполнять аналогичные операции как над столбцовыми таблицами, так и над традиционными строковыми таблицами (кучами) в большинстве случаев.

  • Поддержка журнала предварительной записи (WAL)

  • Поддержка транзакций ROLLBACK;

  • Поддержка физической репликации;

  • Поддержка восстановления, включая восстановление до определенного момента времени (PITR);

  • Поддержка pg_dump и pg_upgrade без необходимости специальных операций или дополнительных шагов;

  • Поддерживает большую часть функциональности, которая работает с обычными кучевыми таблицами (строками).

Ограничения

  • Только для добавления (UPDATE/DELETE операции не поддерживаются);

  • Не освобождает пространство (например, откатившиеся транзакции могут все еще занимать дисковое пространство);

  • Поддержка сканирования битовых индексов отсутствует;

  • Не поддерживается tidscans;

  • Поддержка выборочного сканирования отсутствует;

  • Метод хранения TOAST не поддерживается (большие значения хранятся внутренне);

  • ON CONFLICT не поддерживается (кроме DO NOTHING без указания цели);

  • Уровень строковых блокировок не поддерживается (SELECT ... FOR SHARE, SELECT ... FOR UPDATE);

  • Уровень изоляции serializable не поддерживается.

  • Поддерживаются версии Tantor SE 14+;

  • Внешние ключи, ограничения уникальности или ограничение-исключения не поддерживаются.

  • Логическое декодирование не поддерживается;

  • Параллельные сканирования внутри узла не поддерживаются;

  • AFTER ... FOR EACH ROW триггеры не поддерживаются;

  • Непротоколируемые колоночные таблицы не поддерживаются.

Со временем, перечисленные выше ограничения будут постепенно сниматься.

F.21.6. Использование колоночного формата #

Создание колоночной таблицы, указывая USING columnar опцию: 

CREATE TABLE my_columnar_table
(
    id INT,
    i1 INT,
    i2 INT8,
    n NUMERIC,
    t TEXT
) USING columnar;

Вставить данные в таблицу и прочитать их как обычно (с учетом ограничений, перечисленных выше).

Чтобы просмотреть внутреннюю статистику для таблицы, используйте VACUUM VERBOSE. Обратите внимание, что VACUUM (без FULL) гораздо быстрее на колоночной таблице, потому что он сканирует только метаданные, а не фактические данные.

Установите параметры с помощью ALTER TABLE: 

ALTER TABLE my_columnar_table SET
  (columnar.compression = none, columnar.stripe_row_limit = 10000);

Доступны следующие опции:

  • columnar.compression : [none|pglz|zstd|lz4|lz4hc] - установите тип сжатия для новых вставленных данных. Существующие данные не будут пересжаты/распакованы. Значение по умолчанию - zstd (если поддержка была указана при компиляции).

  • columnar.compression_level <integer> - устанавливает уровень сжатия. Допустимые значения от 1 до 19. Если метод сжатия не поддерживает выбранный уровень, вместо него будет выбран ближайший уровень.

  • columnar.stripe_row_limit : <integer> - Максимальное количество строк в полосе для вновь вставленных данных. Существующие полосы данных не будут изменены и могут содержать больше строк, чем это максимальное значение. Значение по умолчанию - 150000.

  • columnar.chunk_group_row_limit : <integer> - максимальное количество строк в блоке для вновь вставленных данных. Существующие блоки данных не будут изменены и могут содержать больше строк, чем это максимальное значение. Значение по умолчанию - 10000.

Вы можете просмотреть все эти параметры, используя запрос ниже: 

SELECT * FROM columnar.options;

Вы также можете установить значения с помощью команды SET для одного из следующих параметров GUC:

  • columnar.compression

  • columnar.compression_level

  • columnar.stripe_row_limit

  • columnar.chunk_group_row_limit

Опции GUC влияют только на вновь созданные таблицы, а не на вновь созданные полосы в существующих таблицах.

F.21.6.1. Секционирование #

Табличные столбцы могут использоваться как секции; и секционированная таблица может состоять из любой комбинации кучи и разделов столбцов. 

CREATE TABLE parent(ts timestamptz, i int, n numeric, s text)
  PARTITION BY RANGE (ts);

-- columnar partition
CREATE TABLE p0 PARTITION OF parent
  FOR VALUES FROM ('2020-01-01') TO ('2020-02-01')
  USING COLUMNAR;
-- columnar partition
CREATE TABLE p1 PARTITION OF parent
  FOR VALUES FROM ('2020-02-01') TO ('2020-03-01')
  USING COLUMNAR;
-- row partition
CREATE TABLE p2 PARTITION OF parent
  FOR VALUES FROM ('2020-03-01') TO ('2020-04-01');

INSERT INTO parent VALUES ('2020-01-15', 10, 100, 'one thousand'); -- columnar
INSERT INTO parent VALUES ('2020-02-15', 20, 200, 'two thousand'); -- columnar
INSERT INTO parent VALUES ('2020-03-15', 30, 300, 'three thousand'); -- row

При выполнении операций с секционированной таблицей, состоящей из строк и столбцов, обратите внимание на следующие особенности операций, которые поддерживаются для строк, но не для столбцов (например, UPDATE, DELETE, блокировки строк и т.д.):

  • Если операция пытается выполниться на определенной таблице с методом хранения кучи (например, UPDATE p2 SET i = i + 1), она будет успешной; но если она пытается выполниться на таблице, организованной в столбцы (например UPDATE p1 SET i = i + 1), она не пройдет.

  • Если операция пытается выполниться на секционированной таблице и имеет предложение WHERE, которое исключает все таблицы, сохраненные в виде столбцов (например, UPDATE parent SET i = i + 1 WHERE ts = '2020-03-15'), она будет успешно выполнена.

  • Если операция пытается выполниться на секционированной таблице, но не исключает все разделы столбцов, она не пройдет; даже если фактические данные для обновления затрагивают только кучные таблицы (например, UPDATE parent SET i = i + 1 WHERE n = 300).

Обратите внимание, что Columnar поддерживает индексы btree и hash (и ограничения, которые требуют их), но не поддерживает индексы gist, gin, spgist и brin. По этой причине, если некоторые таблицы имеют колоночный формат и если индекс не поддерживается, невозможно создать индексы непосредственно на секционированной (родительской) таблице. В таком случае вам нужно создать индекс на отдельных кучных таблицах. То же самое касается ограничений, которые требуют индексы, например: 

CREATE INDEX p2_ts_idx ON p2 (ts);
CREATE UNIQUE INDEX p2_i_unique ON p2 (i);
ALTER TABLE p2 ADD UNIQUE (n);

F.21.6.2. Преобразование между кучей и колоночным форматом #

Примечание

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

CREATE TABLE my_table(i INT8 DEFAULT '7');
INSERT INTO my_table VALUES(1);
-- convert to columnar
SELECT alter_table_set_access_method('my_table', 'columnar');
-- back to row
SELECT alter_table_set_access_method('my_table', 'heap');

F.21.7. Микробенчмарк производительности #

Важно

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

Схема: 

CREATE TABLE perf_row(
    id INT8,
    ts TIMESTAMPTZ,
    customer_id INT8,
    vendor_id INT8,
    name TEXT,
    description TEXT,
    value NUMERIC,
    quantity INT4
);

CREATE TABLE perf_columnar(LIKE perf_row) USING COLUMNAR;

Функции для генерации данных: 

CREATE OR REPLACE FUNCTION random_words(n INT4) RETURNS TEXT LANGUAGE plpython2u AS $$
import random
t = ''
words = ['zero','one','two','three','four','five','six','seven','eight','nine','ten']
for i in xrange(0,n):
  if (i != 0):
    t += ' '
  r = random.randint(0,len(words)-1)
  t += words[r]
return t
$$;

Вставьте данные используя функцию random_words: 

INSERT INTO perf_row
   SELECT
    g, -- id
    '2020-01-01'::timestamptz + ('1 minute'::interval * g), -- ts
    (random() * 1000000)::INT4, -- customer_id
    (random() * 100)::INT4, -- vendor_id
    random_words(7), -- name
    random_words(100), -- description
    (random() * 100000)::INT4/100.0, -- value
    (random() * 100)::INT4 -- quantity
   FROM generate_series(1,75000000) g;

INSERT INTO perf_columnar SELECT * FROM perf_row;

Проверьте уровень сжатия: 

=> SELECT pg_total_relation_size('perf_row')::numeric/pg_total_relation_size('perf_columnar') AS compression_ratio;

 compression_ratio
--------------------
 5.3958044063457513
(1 row)

Общий коэффициент сжатия столбцовой таблицы по сравнению с теми же данными, хранящимися в куче, составляет 5.4X. 

=> VACUUM VERBOSE perf_columnar;
INFO:  statistics for "perf_columnar":
storage id: 10000000000
total file size: 8761368576, total data size: 8734266196
compression rate: 5.01x
total row count: 75000000, stripe count: 500, average rows per stripe: 150000
chunk count: 60000, containing data for dropped columns: 0, zstd compressed: 60000

VACUUM VERBOSE сообщает о более низком коэффициенте сжатия, потому что он усредняет коэффициент сжатия отдельных блоков и не игнорирует экономию метаданных формата столбца.

Характеристики системы:

  • Azure VM: Стандарт D2s v3 (2 виртуальных процессора, 8 ГиБ памяти)

  • Linux (ubuntu 18.04)

  • Дисковый накопитель: Стандартный HDD (512ГБ, макс. 500 IOPS, макс. 60 МБ/с)

  • PostgreSQL 13 (--with-llvm, --with-python)

  • shared_buffers = 128MB

  • max_parallel_workers_per_gather = 0

  • jit = on

Примечание

Поскольку это было выполнено на системе с достаточным объемом физической памяти для хранения большой части таблицы, преимущества столбцового ввода-вывода не были бы полностью реализованы во время выполнения запроса, если размер данных значительно не увеличивается.

Запросы приложения: 

-- OFFSET 1000 so that no rows are returned, and we collect only timings

SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_row GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
SELECT vendor_id, SUM(quantity) FROM perf_columnar GROUP BY vendor_id OFFSET 1000;
Timing (median of three runs):

  • строка: 436с

  • колоночный: 16s

  • ускорение: 27X

F.21.8. Общие рекомендации #

F.21.8.1. Выбор размера полосы (Stripe Size) и размера блока (Chunk Size) #

Определение оптимальных размеров полос и блоков для columnar таблиц в Tantor SE зависит от различных факторов, таких как характер данных, шаблоны запросов и характеристики оборудования. Вот некоторые рекомендации:

  • Размер полосы Оптимальный размер «полосы» может зависеть от типичного размера ваших запросов. Если запросы обычно возвращают большое количество строк, больший размер «полосы» может быть более эффективным, поскольку он сокращает количество операций ввода/вывода. С другой стороны, если запросы часто возвращают небольшое подмножество данных, предпочтительнее может быть меньший размер «полосы».

  • Размер блока : Определяет, сколько данных будет сжиматься за раз. Меньший размер блока может привести к более высокому коэффициенту сжатия, но может увеличить издержки на сжатие. Больший размер блока может снизить издержки, но потенциально уменьшить коэффициент сжатия.

  • Тестирование и настройка : Важно проводить тестирование с реальными данными и запросами, чтобы определить оптимальные настройки для вашей конкретной ситуации. Можно начать с рекомендуемых настроек по умолчанию, а затем экспериментировать с различными размерами полос и блоков, чтобы увидеть, как это влияет на производительность запросов и коэффициенты сжатия.

  • Свойства оборудования : Стоит также учесть характеристики оборудования, такие как пропускная способность диска и процессора, так как это может влиять на то, какие размеры полос и блоков будут наиболее эффективными.

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

F.21.8.2. Алгоритмы сжатия #

Выбор алгоритма сжатия для columnar таблиц в Tantor SE зависит от нескольких факторов, включая характер данных, требования к производительности, и технические характеристики оборудования. Вот несколько рекомендаций, которые могут помочь вам принять решение:

  • none: Этот тип сжатия не применяет никакого сжатия к данным. Он может быть полезен, если ваши данные уже сжаты, или если у вас очень высокие требования к производительности и достаточно дискового пространства.

  • lz4: обеспечивает быстрое сжатие и распаковку данных. Это может быть полезно, если у вас высокие требования к производительности, но вы все же хотите сэкономить немного дискового пространства.

  • zstd: предлагает более высокое соотношение сжатия по сравнению с LZ4, но требует больше процессорного времени для сжатия и распаковки данных. Этот алгоритм может быть полезен, если у вас ограниченное дисковое пространство и вы готовы потратить немного больше процессорного времени на сжатие данных.

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

F.21.9. Работа с Time Series-данными #

F.21.9.1. Создание таблиц #

Time Series-данные характеризуются последовательной записью значений по возрастанию во времени. Для таких данных важна упорядоченность.

Создайте необходимые расширения: 

create extension pg_columnar;
create extension plpython3u;

Создайте тестовую таблицу: 

CREATE TABLE perf_columnar(id INT8,
ts TIMESTAMPTZ,
customer_id INT8,
vendor_id INT8,
name TEXT,
description TEXT,
value NUMERIC,
quantity INT4) USING columnar;

ALTER TABLE public.perf_columnar SET
  (columnar.compression = lz4, columnar.stripe_row_limit = 100000, columnar.chunk_group_row_limit = 10000);

Создайте функцию для генерации случайного текста: 

CREATE OR REPLACE FUNCTION random_words(n INT4) RETURNS TEXT LANGUAGE plpython3u AS $$
import random
t = ''
words = ['zero','one','two','three','four','five','six','seven','eight','nine','ten']
for i in range(0, n):
  if (i != 0):
    t += ' '
  r = random.randint(0, len(words) - 1)
  t += words[r]
return t
$$;

Сгенерируйте тестовые данные: 

INSERT INTO perf_columnar
   SELECT
    g, -- id
    '2023-06-01'::timestamptz + ('1 minute'::interval * g), -- ts
    (random() * 1000000)::INT4, -- customer_id
    (random() * 100)::INT4, -- vendor_id
    random_words(7), -- name
    random_words(100), -- description
    (random() * 100000)::INT4/100.0, -- value
    (random() * 100)::INT4 -- quantity
   FROM generate_series(1,7500000) g;

Как видно из приведенного выше запроса, мы вставили данные, отсортированные по ts. Соберите статистику с помощью VACUUM (ANALYZE, VERBOSE): 

VACUUM ANALYZE perf_columnar;

Создайте копию таблицы с другими параметрами: 

CREATE TABLE perf_columnar2(LIKE perf_columnar) USING COLUMNAR;

ALTER TABLE public.perf_columnar2 SET
  (columnar.compression = zstd, columnar.stripe_row_limit = 10000, columnar.chunk_group_row_limit = 1000);

INSERT INTO perf_columnar2 SELECT * FROM perf_columnar;

VACUUM ANALYZE perf_columnar2;

Проверьте размер получившихся таблиц: 

test_db=# \dt+
                                        List of relations
 Schema |      Name      | Type  |  Owner   | Persistence | Access method |  Size   | Description 
--------+----------------+-------+----------+-------------+---------------+---------+-------------
 public | perf_columnar  | table | postgres | permanent   | columnar      | 1886 MB | 
 public | perf_columnar2 | table | postgres | permanent   | columnar      | 850 MB  | 
(2 rows)

Как видно, таблица perf_columnar в два раза больше, чем perf_columnar2.

Проверьте параметры таблиц: 

test_db=# select * from columnar.options;

    relation    | chunk_group_row_limit | stripe_row_limit | compression | compression_level 
----------------+-----------------------+------------------+-------------+-------------------
 perf_columnar  |                 10000 |           100000 | lz4         |                 3
 perf_columnar2 |                  1000 |            10000 | zstd        |                 3

(2 rows)

F.21.9.2. Сравнение производительности запросов #

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

explain (analyze, verbose, buffers) select ts from perf_columnar
where ts < '2023-06-01 10:00:00'::timestamp with time zone and ts > '2023-06-01 10:00:05'::timestamp with time zone;
                                                                           QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar  (cost=0.00..401.12 rows=4 width=8) (actual time=18.838..49.001 rows=4 loops=1)
   Output: ts
   Filter: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 9996
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 749     <-----------
   Buffers: shared hit=3833 read=264                <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=52 read=7
 Planning Time: 12.789 ms
 Execution Time: 49.188 ms
(13 rows)

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

explain (analyze, verbose, buffers) select ts from perf_columnar2
where ts < '2020-01-01 10:00:00'::timestamp with time zone and ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Custom Scan (ColumnarScan) on public.perf_columnar2  (cost=0.00..17.95 rows=52 width=8) (actual time=5.175..58.532 rows=49 loops=1)
   Output: ts
   Filter: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Rows Removed by Filter: 951
   Columnar Projected Columns: ts
   Columnar Chunk Group Filters: ((ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Columnar Chunk Groups Removed by Filter: 7499    <-----------
   Buffers: shared hit=40824 read=1                 <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=155
 Planning Time: 1.086 ms
 Execution Time: 58.717 ms
(13 rows)

Обратите внимание на Columnar Chunk Groups Removed by Filter: 749 в первом случае и 7499 во втором. Однако, количество прочитанных буферов во втором запросе гораздо выше.

Терминология для понимания поведения этих запросов:

  • Stripe: Этот тип не применяет никакого сжатия к данным. Он может быть полезен, если ваши данные уже сжаты, или если у вас очень высокие требования к производительности и достаточно дискового пространства.

  • Chunk Group: полосы разбиваются на группы блоков по 10 000 строк (по умолчанию).

  • Chunk: Каждая группа блоков состоит из одного блока для каждого столбца. Chunk является единицей сжатия, и для каждого блока отслеживаются минимальные/максимальные значения, что позволяет фильтровать группы блоков (Chunk Group Filtering).

  • Chunk Group Filtering: когда предложение запроса WHERE не соответствует ни одному кортежу в блоке, и можно определить это по минимальному/максимальному значению для блока, тогда фильтрация группы блоков просто пропускает всю группу блоков без ее распаковки.

Как мы видим, были отфильтрованы группы блоков 749/7499, что означает, что строки 7490000/7499000 были отфильтрованы без необходимости извлечения или распаковки данных. Только одна группа блоков (10 000 и 1 000 строк) требовала извлечения и распаковки, поэтому запрос занял всего несколько миллисекунд.

Но, как видно из планов запросов, в первом случае было использовано 30 МБ, а во втором - в десять раз больше - 319 МБ.

F.21.9.3. Использование индексов #

Columnar поддерживает индексы btree и hash (и ограничения, требующие их), но не поддерживает типы индексов, такие как gist, gin, spgist и brin.

Рассмотрите возможность создания индексов для таблиц perf_columnar и perf_columnar2: 

create index test on perf_columnar2 (ts);
create index test_h on perf_columnar (ts);

Проверьте размер индексов: 

test_db=# \di+
                                            List of relations

 Schema |  Name  | Type  |  Owner   |     Table      | Persistence | Access method |  Size  | Description 
--------+--------+-------+----------+----------------+-------------+---------------+--------+-------------
 public | test   | index | postgres | perf_columnar2 | permanent   | btree         | 161 MB | 
 public | test_h | index | postgres | perf_columnar  | permanent   | btree         | 161 MB | 
(2 rows)

Размер индексов одинаковый.

Отключите принудительное использование Custom Scan: 

SET columnar.enable_custom_scan TO OFF;

Повторно выполните типичный запрос для чтения набора записей из таблицы perf_columnar для определенного временного интервала: 

explain (analyze, verbose, buffers) select ts from perf_columnar where ts < '2023-06-01 10:00:00'::timestamp with time zone and ts > '2023-06-01 10:00:05'::times
tamp with time zone;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_h on public.perf_columnar  (cost=0.43..3217.48 rows=4 width=8) (actual time=402.144..402.204 rows=4 loops=1)
   Output: ts
   Index Cond: ((perf_columnar.ts < '2023-06-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar.ts > '2023-06-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=181 read=3232      <-----------
 Query Identifier: 1607994334608619710
 Planning:
   Buffers: shared hit=20 read=5
 Planning Time: 16.278 ms
 Execution Time: 402.386 ms
(9 rows)

Почти такое же количество буферов было просканировано, как при использовании Custom Scan.

Повторно выполните типичный запрос для чтения набора записей из таблицы perf_columnar2 для определенного временного интервала: 

explain (analyze, verbose, buffers) select ts from perf_columnar2 where ts < '2020-01-01 10:00:00'::timestamp with time zone and ts > '2020-01-01 10:00:05'::timestamp with time zone;
                                                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test on public.perf_columnar2  (cost=0.43..153.05 rows=52 width=8) (actual time=14.620..14.821 rows=49 loops=1)
   Output: ts
   Index Cond: ((perf_columnar2.ts < '2020-01-01 10:00:00+03'::timestamp with time zone) AND (perf_columnar2.ts > '2020-01-01 10:00:05+03'::timestamp with time zone))
   Buffers: shared hit=372 read=145     <-----------
 Query Identifier: -801549076851693482
 Planning:
   Buffers: shared hit=97
 Planning Time: 0.813 ms
 Execution Time: 14.978 ms
(13 rows)

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

F.21.9.4. Заключения #

Можно сделать следующие выводы:

  • Самым эффективным методом сжатия данных является zstd;

  • Размер полосы и блока влияет на количество буферов, сканируемых с использованием метода Chunk Group Filtering;

  • При чтении небольшого объема данных использование индекса может оказаться более эффективным;

  • Для проверки гипотезы необходимо принудительно включить использование индексов с помощью команды SET columnar.enable_custom_scan TO OFF;

  • Последовательная запись данных TS может значительно сократить размер индексов и объем распакованных блоков (Chunk). Поэтому рекомендуется сортировать данные перед их вставкой в базу данных или использовать кластеризацию (cluster).

Назад Наверх Далее
F.20. hstore — hstore ключ/значение тип данных Начало F.22. HypoPG