Início / dba / Perguntas / 159710
Accepted
Evan Carroll
Asked: 2017-01-02 15:03:21 +0800 CST

Por que array_agg() é mais lento que o construtor ARRAY() não agregado?

  • 772

Eu estava revisando um código antigo escrito para PostgreSQL pré-8.4 e vi algo realmente bacana. Lembro-me de ter uma função personalizada fazendo isso antigamente, mas esqueci como array_agg()era pré-. Para revisão, a agregação moderna é escrita assim.

SELECT array_agg(x ORDER BY x DESC) FROM foobar;

No entanto, era uma vez, foi escrito assim,

SELECT ARRAY(SELECT x FROM foobar ORDER BY x DESC);

Então, eu tentei com alguns dados de teste.

CREATE TEMP TABLE foobar AS
SELECT * FROM generate_series(1,1e7)
  AS t(x);

Os resultados foram surpreendentes. A maneira #OldSchoolCool foi incrivelmente mais rápida: uma aceleração de 25%. Além disso, simplificá-lo sem a ORDEM, mostrou a mesma lentidão.

# EXPLAIN ANALYZE SELECT ARRAY(SELECT x FROM foobar);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Result  (cost=104425.28..104425.29 rows=1 width=0) (actual time=1665.948..1665.949 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.032..716.793 rows=10000000 loops=1)
 Planning time: 0.068 ms
 Execution time: 1671.482 ms
(5 rows)

test=# EXPLAIN ANALYZE SELECT array_agg(x) FROM foobar;
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=119469.60..119469.61 rows=1 width=32) (actual time=2155.154..2155.154 rows=1 loops=1)
   ->  Seq Scan on foobar  (cost=0.00..104425.28 rows=6017728 width=32) (actual time=0.031..717.831 rows=10000000 loops=1)
 Planning time: 0.054 ms
 Execution time: 2174.753 ms
(4 rows)

Então, o que está acontecendo aqui. Por que array_agg é uma função interna muito mais lenta que o voodoo SQL do planejador?

Usando " PostgreSQL 9.5.5 em x86_64-pc-linux-gnu, compilado por gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64 bits"

postgresql aggregate

2 respostas

  1. Best Answer

    Não há nada "old school" ou "desatualizado" sobre um construtor ARRAY (isso é o que ARRAY(SELECT x FROM foobar)é). É moderno como sempre. Use-o para agregação de matriz simples.

    O manual:

    Também é possível construir um array a partir dos resultados de uma subconsulta. Nesse formulário, o construtor de matriz é escrito com a palavra-chave ARRAYseguida por uma subconsulta entre parênteses (não entre colchetes).

    A função de agregaçãoarray_agg() é mais versátil na medida em que pode ser integrada em uma SELECTlista com mais colunas, possivelmente mais agregações na mesma SELECT, e grupos arbitrários podem ser formados com GROUP BY. Enquanto um construtor ARRAY só pode retornar uma única matriz de SELECTuma única coluna retornada.

    Não estudei o código-fonte, mas parece óbvio que uma ferramenta muito mais versátil também é mais cara.

    Uma diferença notável: o construtor ARRAY retorna um array vazio ( {}) se nenhuma linha se qualificar. array_agg()retorna NULLpara o mesmo.

    • 25

  2. Acredito que a resposta aceita por Erwin poderia ser adicionada com o seguinte.

    Normalmente, estamos trabalhando com tabelas regulares com índices, ao invés de tabelas temporárias (sem índices) como na pergunta original. É útil observar que agregações, como ARRAY_AGG, não podem alavancar índices existentes quando a classificação é feita durante a agregação .

    Por exemplo, suponha a seguinte consulta:

    SELECT ARRAY(SELECT c FROM t ORDER BY id)

    Se tivermos um índice em t(id, ...), o índice pode ser usado, em favor de uma varredura sequencial em tseguida por uma classificação em t.id. Além disso, se a coluna de saída que está sendo agrupada na matriz (aqui c) fizer parte do índice (como um índice em t(id, c)ou um índice de inclusão em t(id) include(c)), isso pode até ser uma varredura somente de índice.

    Agora, vamos reescrever essa consulta da seguinte forma:

    SELECT ARRAY_AGG(c ORDER BY id) FROM t

    Agora, a agregação não usará o índice e terá que classificar as linhas na memória (ou pior ainda, para grandes conjuntos de dados, no disco). Isso sempre será uma varredura sequencial tseguida por agregação+classificação .

    Tanto quanto eu sei, isso não está documentado na documentação oficial, mas pode ser derivado da fonte. Este deve ser o caso de todas as versões atuais, incluindo a v11.

    • 12

relate perguntas