Wydajne łączenie (usuwanie duplikatów) tablic

Mam dwa stoły left2i right2. Obie tabele będą duże (1-10 mln wierszy).

CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER);
ALTER TABLE left2 ADD PRIMARY KEY (id,t1);

CREATE TABLE right2( t1 INTEGER, d INTEGER, arr INTEGER[] );
ALTER TABLE right2 ADD PRIMARY KEY(t1,d);

Wykonam ten typ zapytania:

SELECT l.d + r.d,
       UNIQ(SORT((array_agg_mult(r.arr)))
FROM left2 l,
     right2 r
WHERE l.t1 = r.t1
GROUP BY l.d + r.d
ORDER BY l.d + r.d;

Gdzie do agregacji tablic używam funkcji:

CREATE AGGREGATE array_agg_mult(anyarray) (
SFUNC=array_cat,
STYPE=anyarray,
INITCOND='{}');

Po połączeniu tablic korzystam z UNIQfunkcji intarraymodułu. Czy istnieje bardziej skuteczny sposób to zrobić? Czy w arrpolu jest jakiś indeks przyspieszający scalanie (z usuwaniem duplikatów)? Czy funkcja agregująca może bezpośrednio usuwać duplikaty? Oryginalne tablice można uznać za posortowane (i są unikalne), jeśli to pomaga.

Fiddle SQL jest tutaj :

Prawidłowe wyniki?

Po pierwsze: poprawność. Chcesz stworzyć szereg unikalnych elementów? Twoje obecne zapytanie tego nie robi. Funkcja uniq()z modułu intarray obiecuje tylko:

usuń sąsiadujące duplikaty

Jak nakazuje to instrukcja , musisz:

SELECT l.d + r.d, uniq(sort(array_agg_mult(r.arr)))
FROM   ...

Daje także posortowane tablice - zakładając, że tego chcesz, nie wyjaśniłeś.

Widzę, że masz sort() skrzypce , więc może to być literówka w twoim pytaniu.

Postgres 9.5

Tak czy inaczej, spodoba ci się nowy Postgres 9.5 (obecnie beta). Zapewnia możliwości array_agg_mult()gotowego i znacznie szybszego:

• Wybieranie danych do tablicy Postgres
• Czy w PostgreSQL jest coś takiego jak funkcja zip (), która łączy dwie tablice?

Wprowadzono również inne ulepszenia wydajności w zakresie obsługi tablicy.

Pytanie

Głównym celem array_agg_mult()jest agregacja tablic wielowymiarowych, ale i tak powstają tablice 1-wymiarowe. Chciałbym więc przynajmniej spróbować tego alternatywnego zapytania:

SELECT l.d + r.d AS d_sum, array_agg(DISTINCT elem) AS result_arr
FROM   left2  l
JOIN   right2 r USING (t1)
     , unnest(r.arr) elem
GROUP  BY 1
ORDER  BY 1;

Co również dotyczy twojego pytania:

Czy funkcja agregująca może bezpośrednio usuwać duplikaty?

Tak, może DISTINCT. Nie jest to jednak szybsze niż w uniq()przypadku tablic całkowitoliczbowych, które zostały zoptymalizowane dla tablic całkowitoliczbowych, podczas gdy DISTINCTjest ogólne dla wszystkich kwalifikujących się typów danych.

Nie wymaga intarraymodułu. Jednak wynik niekoniecznie jest posortowany. Postgres używa różnych algorytmów dla DISTINCT(IIRC), duże zestawy są zwykle mieszane, a następnie wynik nie jest sortowany, chyba że dodasz wyraźne ORDER BY. Jeśli potrzebujesz posortowane tablice, to mógłby dodać ORDER BYdo łącznej funkcji bezpośrednio:

array_agg(DISTINCT elem ORDER BY elem)

Ale zwykle jest to wolniejsze niż podawanie wstępnie posortowanych danych array_agg()(jeden duży sort w porównaniu z wieloma małymi). Więc posortowałbym podzapytanie, a następnie agregował:

SELECT d_sum, uniq(array_agg(elem)) AS result_arr
FROM  (
   SELECT l.d + r.d AS d_sum, elem
   FROM   left2  l
   JOIN   right2 r USING (t1)
        , unnest(r.arr) elem
   ORDER  BY 1, 2
   ) sub
GROUP  BY 1
ORDER  BY 1;

To był najszybszy wariant w moim pobieżnym teście na Postgres 9.4.

Fiddle SQL oparty na tym, który podałeś.

Indeks

Nie widzę tutaj dużego potencjału dla żadnego indeksu. Jedyną opcją byłoby:

CREATE INDEX ON right2 (t1, arr);

Ma to sens tylko wtedy, gdy uzyskuje się z tego skany tylko indeksowe - co stanie się, jeśli podstawowa tabela right2jest znacznie szersza niż tylko te dwie kolumny, a twoja konfiguracja kwalifikuje się do skanów tylko indeksowych. Szczegóły na Wiki Postgres.

Jestem naprawdę rozczarowany, jest to łatwa rzecz w Microsoft Access. Możesz utworzyć zapytanie „usuń duplikaty”, a następnie spójrz na SQL, aby zobaczyć, jak to robi. Będę musiał odpalić komputer z systemem Windows, aby sprawdzić. Różnią się, kreator zapytań to robi.

Myślę, że jedną rzeczą jest załadowanie wszystkich danych do jednej tabeli, a następnie WYBIERZ ODRÓŻNIENIE do nowej tabeli. Możesz również trzymać się kolejności według klauzuli, gdy jesteś przy niej. Zrobiłem to jakoś rok temu, to musi być to.

Łączę dane temperaturowe z 2 lat, czujnik wysyła 2 kopie tego samego punktu danych co minutę jako rezerwowe zabezpieczenie. Czasami ktoś zostaje zniszczony, ale ja chcę go tylko zatrzymać. Mam także nakładki między plikami.

Jeśli dane mają dokładnie ten sam format przez cały czas, na maszynie uniksowej możesz zrobić coś takiego

cat *.tab > points.txt
sort -n < points.txt > sorted.txt
uniq -u sorted.txt unique.txt

Ale uniq porównuje linie jako łańcuchy i na przykład 18,7000 to nie to samo co 18,7. Zmieniłem oprogramowanie w ciągu 2 lat, więc mam oba formaty.