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

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

Это объясняется несколькими факторами:

Агрегаты (COUNT, SUM, AVG), предложения WHERE, массовые операции INSERT, UPDATE, DELETE и другие.

Частые крупные обновления, небольшие транзакции.

Hydra Columnar поддерживает как строковые, так и колоночные таблицы, и любой формат хранения имеет свои минусы.

Колоночный формат не подходит для:

Если хранения в колонках не подходит для ваших задач, можно использовать обычные таблицы heap. С Hydra Columnar вы можете использовать оба метода хранения данных!

Попробуйте инкрементные материализованные представления.

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

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

Установите расширение columnar, выполнив следующий запрос от имени суперпользователя:

CREATE EXTENSION IF NOT EXISTS pg_columnar;

После установки для использования колоночных таблиц не требуются права суперпользователя.

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

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

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

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

Установите параметры таблицы, используя columnar.alter_columnar_table_set:

SELECT columnar.alter_columnar_table_set(
  'my_columnar_table',
  compression => 'none',
  stripe_row_limit => 10000
);

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

Можно просмотреть параметры всех таблиц, используя:

SELECT * from columnar.options;

Или конкретной таблицы:

SELECT * FROM columnar.options
WHERE regclass = 'my_columnar_table'::regclass;

Можно сбросить один или несколько параметров таблицы до их значений по умолчанию (или текущих значений, установленных командой SET) с помощью columnar.alter_columnar_table_reset:

SELECT columnar.alter_columnar_table_reset(
  'my_columnar_table',
  chunk_group_row_limit => true
);

Установите общие параметры columnar, используя SET:

SET columnar.compression TO 'none';
SET columnar.enable_parallel_execution TO false;
SET columnar.compression TO default;

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

Тип таблицы по умолчанию для базы данных по умолчанию - heap. Вы можете изменить тип таблицы по умолчанию, используя default_table_access_method:

SET default_table_access_method TO 'columnar';

Теперь для любой таблицы, которую вы создаете вручную с помощью CREATE TABLE, тип по умолчанию будет columnar.

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

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, блокировки строк и т.д.):

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

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

Установите plpython3u перед запуском теста:

CREATE EXTENSION plpython3u;

Схема:

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 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
$$;

Вставьте данные, используя функцию 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 сообщает более низкий коэффициент сжатия, потому что он усредняет коэффициент сжатия отдельных блоков и учитывает экономию метаданных колоночного формата.

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

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

-- 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;

Время (медиана из трех запусков):

Тесты производительности проводились на экземпляре c6a.4xlarge (16 vCPU, 32 ГБ ОЗУ) с 500 ГБ хранилища GP2. Результаты в секундах, меньшее значение является лучшим показателем.

Диаграмма F.1. Производительность

Запрос 0 выполняется в 512 раз быстрее:

SELECT COUNT(*) FROM hits;

Запрос 2 выполняется в 283 раза быстрее:

SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;

Функция Postgres COUNT работает медленно? Больше нет! Hydra Columnar параллелизирует и векторизует агрегаты (COUNT, SUM, AVG), чтобы обеспечить скорость анализа, которая необходима в Postgres.

Фильтрация (предложения WHERE):

Запрос 1 выполняется в 1,412 раза быстрее:

SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;

Использование фильтров на колоночном хранилище Hydra привели к улучшению производительности в 1412 раз.

По ссылке Clickbench вы найдете расширенные результаты и список из 42 протестированных запросов.

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

Диаграмма F.2. Скорость запроса

Остальные результаты непрерывных тестирования см. по ссылке BENCHMARKS.

Колоночное хранение является ключевой частью структуры хранилища данных, но почему это так? В этой статье мы рассмотрим, что такое колоночное хранение и почему оно является такой важной частью структуры хранилища данных.

| a | b | c | d |
| a | b | c | d |
| a | b | c | d |

| a | a | a |
| b | b | b |
| c | c | c |
| d | d | d |

Чтобы максимально увеличить производительность запросов SELECT, колоночные таблицы должны иметь максимум данных в каждой полосе. Как и INSERT, каждая транзакция с запросом UPDATE будет записывать новую полосу. Чтобы увеличить размер этих полос до максимума, обновляйте как можно больше данных в одной транзакции. Также можно запустить VACUUM на таблице и объединить самые последние полосы в одну полосу максимального размера.

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

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

SELECT columnar.vacuum('mytable', 25);

SELECT columnar.vacuum_full();

SELECT columnar.vacuum_full('public');
SELECT columnar.vacuum_full(sleep_time => 0.5);
SELECT columnar.vacuum_full(stripe_count => 1000);

Термины, используемые в этом разделе, разъяснены в Раздел 13.2.

Обновления и удаления в колонках Hydra будут соответствовать уровню изоляции, запрашиваемому для текущей транзакции (по умолчанию это READ COMMITTED). Имейте в виду, что запрос UPDATE реализуется как DELETE, за которым следует INSERT. Поскольку новые данные, вставляемые в рамках одной транзакции, могут появиться во второй транзакции в READ COMMITTED, это может повлиять на параллельные транзакции, даже если первая транзакция была UPDATE. Хотя такое поведение удовлетворяет условиям READ COMMITTED, оно может привести к неожиданным результатам. Это также возможно в таблицах heap (строковых таблицах), но таблицы heap содержат дополнительные метаданные, которые ограничивают нежелательные результаты такого поведения.

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

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

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

Эти несжатые колонки затем доступны в течение всего времени выполнения запроса SELECT и управляются непосредственно колоночным хранилищем. ПРИМЕЧАНИЕ: этот кеш не используется для UPDATE, DELETE или INSERT и освобождается после завершения запроса SELECT. #### Включение кеша

кеширование можно включить или отключить с помощью GUC: columnar.enable_column_cache. Можно повысить производительность, отключив кеш для запросов, для которых он не нужно.

-- enable the cache
set columnar.enable_column_cache = 't';

-- disable the cache
set columnar.enable_column_cache = 'f';

Кроме того, размер кеша можно установить, по умолчанию 200MB на процесс.

set columnar.column_cache_size = '2000MB';

Размер варьируется от 20MB до 20000MB и потребляется каждым параллельным процессом. Это означает, что если работает 8 параллельных процессов и установлен размер кеша 2000MB, то запрос может потреблять до 8 * 2000000 байт, или до 16GB оперативной памяти, поэтому очень важно выбрать размер кеша, который подходит для вашей рабочей нагрузки.

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

Для максимально эффективного использования материализованных представлений можно также использовать следующие функции:

Документация в этом разделе взята со страницы pg_ivm.

pg_ivm предоставляет способ поддерживать актуальность материализованных представлений, предполагающий вычисление только инкрементальных изменений и их применение к представлениям, а не перерасчет содержимого с нуля, как это делает REFRESH MATERIALIZED VIEW. Это процесс “инкрементального обслуживания представлений” (или IVM), и он может обновлять материализованные представления более эффективно, чем пересчет, когда изменяются только небольшие части представления.

Существует два варианта обновления представлений: немедленное и отложенное. При немедленном обновлении представления обновляются в той же транзакции, в которой изменяется базовая таблица. При отложенном обновлении представления обновляются после фиксации транзакции, например, когда к представлению осуществляется доступ, в ответ на команду пользователя, типа REFRESH MATERIALIZED VIEW, или периодически в фоновом режиме и т.п.. pg_ivm предоставляет своего рода немедленное обновление, при котором материализованные представления обновляются немедленно в триггерах AFTER, когда изменяется базовая таблица.

Чтобы начать использовать pg_ivm, необходимо сначала включить его в базе данных:

CREATE EXTENSION IF NOT EXISTS pg_ivm;

Мы называем материализованное представление, поддерживающее IVM, инкрементально поддерживаемым материализованным представлением (IMMV). Чтобы создать IMMV, необходимо вызвать функцию create_immv с указанием имени отношения и запросом определения представления. Например:

SELECT create_immv('sales_test', 'SELECT * FROM sales');

создает IMMV с именем ‘sales_test’, определенный как SELECT * FROM sales. Это соответствует следующей команде для создания обычного материализованного представления:

CREATE MATERIALIZED VIEW sales_test AS SELECT * FROM sales;

При создании IMMV, некоторые триггеры создаются автоматически, чтобы содержимое представления немедленно обновлялось при изменении его базовых таблиц.

postgres=# SELECT create_immv('m', 'SELECT * FROM t0');
NOTICE:  could not create an index on immv "m" automatically
DETAIL:  This target list does not have all the primary key columns, or this view does not contain DISTINCT clause.
HINT:  Create an index on the immv for efficient incremental maintenance.
 create_immv
-------------
           3
(1 row)

postgres=# SELECT * FROM m;
 i
---
 1
 2
 3
(3 rows)

postgres=# INSERT INTO t0 VALUES (4);
INSERT 0 1
postgres=# SELECT * FROM m; -- automatically updated
 i
---
 1
 2
 3
 4
(4 rows)

SET default_table_access_method = 'columnar';
SET max_parallel_workers = 1;
SELECT create_immv('sales_test', 'SELECT * FROM sales');

SET max_parallel_workers = DEFAULT;
SET default_table_access_method = DEFAULT;

create_immv(immv_name text, view_definition text) RETURNS bigint

refresh_immv(immv_name text, with_data bool) RETURNS bigint

get_immv_def(immv regclass) RETURNS text

test=# CREATE MATERIALIZED VIEW mv_normal AS
        SELECT a.aid, b.bid, a.abalance, b.bbalance
        FROM pgbench_accounts a JOIN pgbench_branches b USING(bid);
SELECT 10000000

test=# UPDATE pgbench_accounts SET abalance = 1000 WHERE aid = 1;
UPDATE 1
Time: 9.052 ms

test=# REFRESH MATERIALIZED VIEW mv_normal ;
REFRESH MATERIALIZED VIEW
Time: 20575.721 ms (00:20.576)

test=# SELECT create_immv('immv',
        'SELECT a.aid, b.bid, a.abalance, b.bbalance
         FROM pgbench_accounts a JOIN pgbench_branches b USING(bid)');
NOTICE:  created index "immv_index" on immv "immv"
 create_immv
-------------
    10000000
(1 row)

test=# UPDATE pgbench_accounts SET abalance = 1234 WHERE aid = 1;
UPDATE 1
Time: 15.448 ms

test=# SELECT * FROM immv WHERE aid = 1;
 aid | bid | abalance | bbalance
-----+-----+----------+----------
   1 |   1 |     1234 |        0
(1 row)

test=# DROP INDEX immv_index;
DROP INDEX

test=# UPDATE pgbench_accounts SET abalance = 9876 WHERE aid = 1;
UPDATE 1
Time: 3224.741 ms (00:03.225)

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

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

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

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

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;

SELECT columnar.alter_columnar_table_set(
    'public.perf_columnar',
    compression => 'lz4',
    stripe_row_limit => 100000,
    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;

SELECT columnar.alter_columnar_table_set(
    'public.perf_columnar2',
    compression => 'zstd',
    stripe_row_limit => 10000,
    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)

Выполните типичный запрос для чтения набора записей из таблицы 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 во втором. Однако, количество прочитанных буферов во втором запросе гораздо выше.

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

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

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

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 false;

Повторно выполните типичный запрос для чтения набора записей из таблицы 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
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 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)

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

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