Особенности работы с большими массивами¶

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

Итак, тестирование производительности

CREATE OR REPLACE FUNCTION test_arrays() RETURNS void AS
$$
DECLARE
  _start_time timestamp;
  _end_time timestamp;
  _arr varchar[];
  _arr2 varchar[][];
  _max_count constant int := 1000;
  _i int;
  _str varchar;
BEGIN
  _arr := ARRAY(SELECT generate_series(1, 100000)::varchar);

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[1];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[1]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[10];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[10]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[100];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[100]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[1000];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[1000]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[10000];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[10`000]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr[100000];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[100`000]: %', _end_time - _start_time;

  -- Two-dimensional array

  _arr := ARRAY(SELECT generate_series(1, 100)::varchar);
  _arr2 := array[_arr];
  FOR _i IN 1..100
  LOOP
    _arr2 := _arr2 || _arr;
  END LOOP;

   _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr2[10][100];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[10][100]: %', _end_time - _start_time;

  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr2[100][10];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[100][10]: %', _end_time - _start_time;

   _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    _str := _arr2[100][100];
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'array[100][100]: %', _end_time - _start_time;
END
$$ LANGUAGE plpgsql;

SELECT test_arrays();

LOG:  array[1]: 00:00:00.000328
LOG:  array[10]: 00:00:00.000327
LOG:  array[100]: 00:00:00.000641
LOG:  array[1000]: 00:00:00.003673
LOG:  array[10`000]: 00:00:00.023714
LOG:  array[100`000]: 00:00:00.236847
LOG:  array[10][100]: 00:00:00.005668
LOG:  array[100][10]: 00:00:00.031767
LOG:  array[100][100]: 00:00:00.033482

Как видно из результатов тестов время доступа к элемента прямо пропорционально его индексу. Это накладывает серьёзные ограничение на обработку массивов большого размера.

Какие же есть способы, для быстрой работы с большими массивами?

Использование unnest
Для итерации по элементам массива надо использовать unnest
FOR _element IN SELECT unnest(_big_array) LOOP END LOOP;

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

Применение курсоров

На помощь приходят курсоры

OPEN _cursor SCROLL FOR SELECT unnest(_big_array);
FETCH ABSOLUTE _position FROM _cursor INTO _var;

Тест производительности

CREATE OR REPLACE FUNCTION test_cursor_arrays() RETURNS void AS
$$
DECLARE
  _start_time timestamp;
  _end_time timestamp;
  _max_count constant int := 1000;
  _i int;
  _str varchar;
  _arr varchar[];
  _cursor refcursor;
BEGIN
  _arr := ARRAY(SELECT i::varchar FROM  generate_series(1, 10000) AS i);
  OPEN _cursor SCROLL FOR SELECT unnest(_arr);
  _start_time := clock_timestamp();
  FOR _i IN 1.._max_count
  LOOP
    FETCH ABSOLUTE CASE WHEN _i % 1 = 0 THEN 1 ELSE 10000 END FROM _cursor INTO _str;
  END LOOP;
  _end_time := clock_timestamp();
  RAISE LOG 'scroll(+/-10000): %', _end_time - _start_time;
END
$$ LANGUAGE plpgsql;

SELECT test_cursor_arrays();

LOG:  scroll(+/-10000): 00:00:00.002425

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

Использование hstore
Очень удобно работать с псевдомассивами через библиотеку hstore. В роли ключа в этом случае выступает индекс элемента.

Проверим производительность
CREATE OR REPLACE FUNCTION test_hstore_arrays() RETURNS void AS $$ DECLARE _start_time timestamp; _end_time timestamp; _max_count constant int := 1000; _i int; _str varchar; _map hstore; BEGIN _map := hstore(ARRAY(SELECT i::varchar FROM generate_series(1, 20000) AS i)); _start_time := clock_timestamp(); FOR _i IN 1.._max_count LOOP _str := _map -> '5000'; END LOOP; _end_time := clock_timestamp(); RAISE LOG 'hstore: %', _end_time - _start_time; END $$ LANGUAGE plpgsql;
SELECT test_hstore_arrays(); LOG: hstore: 00:00:00.000353
Скорость доступа к 5000 элементу находится на уровне скорости доступа к первому элементу обычного массива. Это самый быстрый вариант из всех рассмотренных. Единственным минусом является необходимость установки дополнительной библиотеки, что не всегда возможно.