Dlaczego array_agg () jest wolniejszy niż nieskumulowany konstruktor ARRAY ()?

Właśnie sprawdzałem stary kod napisany dla PostgreSQL wcześniejszej niż 8.4 i zobaczyłem coś naprawdę fajnego. Pamiętam, że funkcja niestandardowa wykonywała niektóre z tych czynności w ciągu dnia, ale zapomniałam, jak to array_agg()wyglądało. Do przeglądu napisano współczesną agregację.

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

Jednak kiedyś napisano tak:

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

Wypróbowałem to z danymi testowymi.

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

Wyniki były zaskakujące. Sposób #OldSchoolCool był znacznie szybszy: przyspieszenie o 25%. Co więcej, uproszczenie go bez ZAMÓWIENIA wykazało tę samą powolność.

# 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)

Co się tutaj dzieje? Dlaczego array_agg , funkcja wewnętrzna jest o wiele wolniejsza niż voodoo SQL planisty?

Korzystanie z „ PostgreSQL 9.5.5 na x86_64-pc-linux-gnu, skompilowany przez gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit”

Nie ma nic „starej szkoły” lub „nieaktualne” o konstruktora Array (to co ARRAY(SELECT x FROM foobar)jest). Jest nowoczesny jak zawsze. Użyj go do prostej agregacji tablic.

Instrukcja:

Możliwe jest również skonstruowanie tablicy na podstawie wyników podzapytania. W tej formie konstruktor tablicy jest zapisywany ze słowem kluczowym, ARRAYpo którym następuje nawias kwadratowy (nie w nawiasach kwadratowych).

Funkcja agregującaarray_agg() jest bardziej wszechstronna, ponieważ można ją zintegrować z SELECTlistą zawierającą więcej kolumn, być może więcej agregacji w tym samym SELECTi można tworzyć dowolne grupy GROUP BY. Podczas gdy konstruktor ARRAY może zwrócić tylko jedną tablicę z SELECTzwracanej pojedynczej kolumny.

Nie studiowałem kodu źródłowego, ale wydaje się oczywiste, że znacznie bardziej wszechstronne narzędzie jest również droższe.

Jedna zauważalna różnica: konstruktor ARRAY zwraca pustą tablicę ( {}), jeśli nie można zakwalifikować żadnych wierszy. array_agg()zwraca NULLza to samo.

Uważam, że zaakceptowaną odpowiedź Erwina można dodać w następujący sposób.

Zwykle pracujemy ze zwykłymi tabelami z indeksami, zamiast tabel tymczasowych (bez indeksów), jak w pierwotnym pytaniu. Warto zauważyć, że agregacje, takie jak ARRAY_AGG, nie mogą wykorzystywać istniejących indeksów, gdy sortowanie odbywa się podczas agregacji .

Załóżmy na przykład następujące zapytanie:

SELECT ARRAY(SELECT c FROM t ORDER BY id)

Jeśli mamy indeks t(id, ...), można go użyć na korzyść skanowania sekwencyjnego, ta następnie sortowania t.id. Dodatkowo, jeśli kolumna wyjściowa opakowana w tablicę (tutaj c) jest częścią indeksu (takiego jak indeks włączony t(id, c)lub indeks włączenia włączony t(id) include(c)), może to być nawet skanowanie tylko indeksu.

Teraz przepiszmy to zapytanie w następujący sposób:

SELECT ARRAY_AGG(c ORDER BY id) FROM t

Teraz agregacja nie będzie korzystać z indeksu i musi sortować wiersze w pamięci (lub nawet gorzej w przypadku dużych zestawów danych na dysku). Zawsze będzie to skanowanie sekwencyjne, ta następnie agregacja + sortowanie .

O ile mi wiadomo, nie jest to udokumentowane w oficjalnej dokumentacji, ale można je uzyskać ze źródła. Tak powinno być w przypadku wszystkich aktualnych wersji, w tym v11.