@mu уже продемонстрировал, как индекс выражения может решить вашу проблема.
Мое внимание привлекли используемые функции. Оба кажутся излишними для массива из двух целых чисел. Это может быть упрощением реальной ситуации. (?)
В любом случае, я был заинтригован и провел тест с парой вариантов.
Испытательная установка
-- temporary table with 10000 random pairs of integer
CREATE TEMP TABLE arr (i int[]);
INSERT INTO arr
SELECT ARRAY[(random() * 1000)::int, (random() * 1000)::int]
FROM generate_series(1,10000);
Протестируйте кандидатов с кратким комментарием для объяснения каждого из них:
-- 1) mu's query
CREATE OR REPLACE FUNCTION sort_array1(integer[]) RETURNS int[] AS
$$
SELECT array_agg(n) FROM (SELECT n FROM unnest($1) AS t(n) ORDER BY n) AS a;
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 2) simplified with ORDER BY inside aggregate (pg 9.0+)
CREATE OR REPLACE FUNCTION sort_array2(int[]) RETURNS int[] AS
$$
SELECT array_agg(n ORDER BY n) FROM unnest($1) AS t(n);
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 3) uralbash's query
CREATE OR REPLACE FUNCTION sort_array3(anyarray) RETURNS anyarray AS
$$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) g(i)
ORDER BY 1)
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 4) change parameters to int[]
CREATE OR REPLACE FUNCTION sort_array4(int[]) RETURNS int[] AS
$$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(array_lower($1,1), array_upper($1,1)) g(i)
ORDER BY 1)
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 5) simplify array_lower() - it's always 1
CREATE OR REPLACE FUNCTION sort_array5(int[]) RETURNS int[] AS
$$
SELECT ARRAY(
SELECT $1[i]
FROM generate_series(1, array_upper($1,1)) g(i)
ORDER BY 1)
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 6) further simplify to case with 2 elements
CREATE OR REPLACE FUNCTION sort_array6(int[]) RETURNS int[] AS
$$
SELECT ARRAY(
SELECT i
FROM (VALUES ($1[1]),($1[2])) g(i)
ORDER BY 1)
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 7) my radically simple query
CREATE OR REPLACE FUNCTION sort_array7(int[]) RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql STRICT IMMUTABLE;
-- 8) without STRICT modifier
CREATE OR REPLACE FUNCTION sort_array8(int[]) RETURNS int[] AS
$$
SELECT CASE WHEN $1[1] > $1[2] THEN ARRAY[$1[2], $1[1]] ELSE $1 END;
$$ LANGUAGE sql IMMUTABLE;
Полученные результаты
Я выполнил каждую примерно по 20 раз и взял лучший результат у EXPLAIN ANALYZE
.
SELECT sort_array1(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array2(i) FROM arr -- Total runtime: 175 ms
SELECT sort_array3(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array4(i) FROM arr -- Total runtime: 183 ms
SELECT sort_array5(i) FROM arr -- Total runtime: 177 ms
SELECT sort_array6(i) FROM arr -- Total runtime: 144 ms
SELECT sort_array7(i) FROM arr -- Total runtime: 103 ms
SELECT sort_array8(i) FROM arr -- Total runtime: 43 ms (!!!)
Это результаты сервера v9.0.5 на Debian Squeeze. Аналогичные результаты на v.8.4.
Я также протестировал варианты plpgsql, которые оказались немного медленнее, чем ожидалось: слишком много накладных расходов для крошечной операции, нет плана запроса для кэширования.
Простая функция (номер 7) работает значительно быстрее остальных. Этого и следовало ожидать, накладные расходы других вариантов слишком велики для крошечного массива.
Но то, что без модификатора STRICT
более чем удваивает скорость, не ожидалось. По крайней мере, я этого не сделал. Я разместил здесь вопрос об этом явлении.
person
Erwin Brandstetter
schedule
10.12.2011