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