Usar funções para aumentar a eficiência

Por padrão, as funções definidas pelo usuário não são otimizadas para um único fragmento, mas podem ser configuradas para serem executadas como operações de fragmento único. As funções podem encapsular a lógica e garantir que ela seja executada de maneira otimizada para um único fragmento.

Por que as operações de fragmento único são importantes

A utilização de recursos é importante para o desempenho e o custo-benefício. As operações de fragmento único usam significativamente menos recursos em comparação com as operações entre fragmentos. Por exemplo, ao executar uma função para inserir 1 milhão de linhas, a execução de um único fragmento usa aproximadamente 90,5 ACUs, enquanto a execução entre fragmentos usa 126,5 ACUs, uma melhoria de 35% na eficiência dos recursos.

A execução de um único fragmento também oferece:

Operações e funções de fragmento único

As funções são executadas em fragmentos quando um destes pré-requisitos é atendido:

As funções executadas em fragmentos têm melhor desempenho e escalam melhor porque são executadas onde os dados estão localizados.

Funções e volatilidade

Para verificar a volatilidade de uma função, use esta consulta em tabelas de sistema do PostgreSQL:

SELECT DISTINCT nspname, proname, provolatile 
FROM pg_proc PRO 
JOIN pg_namespace NSP ON PRO.pronamespace = NSP.oid 
WHERE proname IN ('random', 'md5');

Resultado do exemplo:

  nspname   | proname | provolatile 
------------+---------+-------------
 pg_catalog | md5     | i
 pg_catalog | random  | v
(2 rows)

Neste exemplo, md5() é imutável e random() é volátil. Isso significa que uma instrução otimizada para um único fragmento que inclua md5() permanece otimizada para um único fragmento, ao contrário de uma instrução que inclua random().

Exemplo com função imutável:

EXPLAIN ANALYZE 
SELECT pg_catalog.md5('123') 
FROM s1.t1 
WHERE col_a = 776586194 
  AND col_b = 654849524 
  AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5';

                          QUERY PLAN 
----------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=3.409..3.409 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 0.313 ms
 Execution Time: 4.253 ms
(4 rows)

Exemplo com função volátil:

EXPLAIN ANALYZE 
SELECT pg_catalog.random() 
FROM s1.t1 
WHERE col_a = 776586194 
  AND col_b = 654849524 
  AND col_c = '3ac2f2affb02987159ccd6ebd23e1ae5';

                          QUERY PLAN 
------------------------------------------------------
 Foreign Scan on t1_fs00001 t1  
   (cost=100.00..15905.15 rows=1 width=8) 
   (actual time=0.658..0.658 rows=1 loops=1)
 Planning Time: 0.263 ms
 Execution Time: 2.892 ms
(3 rows)

A saída mostra que, na função md5(), aplica-se pushdown para executá-la como otimizada para um único fragmento, ao contrário de random().

Funções de distribuição

Uma função que acessa dados em apenas um fragmento deve ser executada nesse fragmento para obter benefícios de desempenho. A função deve ser distribuída e a assinatura da função deve incluir a chave de fragmento completa. Todas as colunas na chave de fragmento devem ser transmitidas como parâmetros à função.

Exemplos de função:

CREATE OR REPLACE FUNCTION s1.func1(
    param_a bigint, 
    param_b bigint, 
    param_c char(100)
) 
RETURNS int AS $$
DECLARE 
    res int;
BEGIN
    SELECT COUNT(*) INTO res
    FROM s1.t1
    WHERE s1.t1.col_a = param_a
      AND s1.t1.col_b = param_b
      AND s1.t1.col_c = param_c;
    
    RETURN res;
END
$$ LANGUAGE plpgsql;

Antes da distribuição, a função não é otimizada para um único fragmento:

EXPLAIN ANALYZE 
SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5');

                                              QUERY PLAN 
------------------------------------------------------------------------------------------------------
 Function Scan on func1  (cost=0.25..0.26 rows=1 width=4) 
                         (actual time=37.503..37.503 rows=1 loops=1)
 Planning Time: 0.901 ms
 Execution Time: 51.647 ms
(3 rows)

Para distribuir a função:

SELECT rds_aurora.limitless_distribute_function(
    's1.func1(bigint,bigint,character)', 
    ARRAY['param_a','param_b','param_c'], 
    's1.t1'
);

Após distribuição, a função é otimizada para um único fragmento:

EXPLAIN ANALYZE 
SELECT * FROM s1.func1(776586194, 654849524, '3ac2f2affb02987159ccd6ebd23e1ae5');

                                           QUERY PLAN 
------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=4.332..4.333 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 0.857 ms
 Execution Time: 5.116 ms
(4 rows)

É possível confirmar a otimização de fragmento único verificando a coluna sso_calls em rds_aurora.limitless_stat_statements:

subcluster_id | subcluster_type | calls | sso_calls |                query 
--------------+-----------------+-------+-----------+--------------------------------------
 2            | router          |     2 |         1 | SELECT * FROM s1.func1( $1, $2, $3 )
 3            | router          |     1 |         1 | SELECT * FROM s1.func1( $1, $2, $3 )
(2 rows)

Funções e padrões de eficiência

Executar a lógica próximo aos dados é mais eficiente, e as funções desempenham um papel fundamental para conseguir isso. Há dois casos de uso principais para melhorar a eficiência com funções:

Extrair a chave de fragmento de dados complexos

Considere uma função com assinatura s3.func3(p_json_doc json) que executa várias operações de banco de dados. Essas operações serão executadas em todos os fragmentos em uma transação que abrange todos os fragmentos. Se o documento JSON contiver a chave de fragmento, será possível criar uma função otimizada para um único fragmento e realizar as operações do banco de dados.

Padrão original:

s3.func3(p_json_doc json)
    database operation 1;
    database operation 2;
    database operation 3;

Padrão otimizado:

s3.func3(p_json_doc json)
DECLARE 
    v_a bigint;
BEGIN
    v_a := (p_json_doc->>'field_a')::bigint;
    SELECT s3.func3_INNER(v_a, p_json_doc);
END;

O que a função interna faz:

s3.func3_INNER(p_a, p_json_doc)
    database operation 1 WHERE shard_key = p_a;
    database operation 2 WHERE shard_key = p_a;
    database operation 3 WHERE shard_key = p_a;

Nesse padrão, a chave de fragmento é encapsulada em um tipo de dados complexo ou pode ser deduzida de outros parâmetros. A lógica, o acesso aos dados e as funções podem determinar, extrair ou criar a chave de fragmento e, em seguida, invocar uma função otimizada para um único fragmento que execute operações relacionadas apenas a um único fragmento. Como a interface da aplicação não muda, a otimização é comparativamente fácil de testar.

Adiar uma chave de fragmento de outras funções ou dados

Outro padrão de design se aplica quando a lógica ou o acesso aos dados calcula ou determina a chave de fragmento. Isso é útil quando uma função pode ser executada em um único fragmento para a maioria das invocações, mas ocasionalmente requeira a execução entre fragmentos.

Padrão original:

NEWORD(INTEGER, …) RETURNS NUMERIC
DECLARE
    all_whid_local := true;
    LOOP through the order lines
        Generate warehouse ID;
        IF generated warehouse ID == input warehouse ID
        THEN
            ol_supply_whid := input warehouse ID;
        ELSE
            all_whid_local := false;
            ol_supply_whid := generated warehouse ID;
        END IF;
        …
    END LOOP;
    …
    RETURN no_s_quantity;

Padrão otimizado com funções separadas:

CREATE OR REPLACE FUNCTION NEWORD_sso(no_w_id INTEGER, …)
RETURNS NUMERIC
…
    RETURN no_s_quantity;
    …
END;
LANGUAGE 'plpgsql';

SELECT rds_aurora.limitless_distribute_function(
    'NEWORD_sso(int,…)', 
    ARRAY['no_w_id'], 
    'warehouse'
);

CREATE OR REPLACE FUNCTION NEWORD_crosshard(no_w_id INTEGER, …)
RETURNS NUMERIC
…
    RETURN no_s_quantity;
    …
END;
LANGUAGE 'plpgsql';

Em seguida, faça com que a função principal chame a versão otimizada para um único fragmento ou a versão entre fragmentos:

IF all_whid_local THEN
    SELECT NEWORD_sso(…) INTO no_s_quantity;
ELSE
    SELECT NEWORD_crosshard(…) INTO no_s_quantity;
END IF;

Essa abordagem permite que a maioria das invocações se beneficie da otimização de fragmento único, mantendo o comportamento correto para casos que exigem execução entre fragmentos.

Verificar operações de fragmento único

Use EXPLAIN para verificar se uma instrução é otimizada para um único fragmento. A saída relata explicitamente “Single Shard Optimized” para operações otimizadas.

Invocação entre fragmentos antes da distribuição:

                       QUERY PLAN 
---------------------------------------------------------------------
 Function Scan on func1  (cost=0.25..0.26 rows=1 width=4) 
                         (actual time=59.622..59.623 rows=1 loops=1)
 Planning Time: 0.925 ms
 Execution Time: 60.211 ms

Invocação de fragmento único após a distribuição:

                       QUERY PLAN 
----------------------------------------------------------------------
 Foreign Scan  (cost=100.00..101.00 rows=100 width=0) 
               (actual time=4.576..4.577 rows=1 loops=1)
 Single Shard Optimized
 Planning Time: 1.483 ms
 Execution Time: 5.404 ms

A diferença nos tempos de execução demonstra o benefício de desempenho da otimização de fragmento único.