Zamiast tworzyć nową tabelę, możesz również ponownie wstawić unikalne wiersze do tej samej tabeli po jej obcięciu. Zrób to wszystko w jednej transakcji . Opcjonalnie możesz automatycznie usunąć tabelę tymczasową na końcu transakcji za pomocą ON COMMIT DROP
. Zobacz poniżej.
To podejście jest przydatne tylko wtedy, gdy istnieje wiele wierszy do usunięcia z całej tabeli. W przypadku kilku duplikatów użyj zwykłego DELETE
.
Wspomniałeś o milionach wierszy. Aby wykonać operację FAST chcesz przeznaczyć wystarczających buforów tymczasowych dla sesji. To ustawienie należy zmienić przed użyciem jakiegokolwiek bufora tymczasowego w bieżącej sesji. Sprawdź rozmiar swojego stołu:
SELECT pg_size_pretty(pg_relation_size('tbl'));
Ustaw temp_buffers
odpowiednio. Zaokrąglij hojnie, ponieważ reprezentacja w pamięci wymaga nieco więcej pamięci RAM.
SET temp_buffers = 200MB;
BEGIN;
CREATE TEMPORARY TABLE t_tmp AS
SELECT DISTINCT * FROM tbl;
TRUNCATE tbl;
INSERT INTO tbl
SELECT * FROM t_tmp;
COMMIT;
Ta metoda może być lepsza niż tworzenie nowej tabeli, jeśli istnieją zależne obiekty. Widoki, indeksy, klucze obce lub inne obiekty odwołujące się do tabeli. TRUNCATE
sprawia, że i tak zaczynasz z czystym kontem (nowy plik w tle) i jest znacznie szybszy niż DELETE FROM tbl
przy dużych stołach (w DELETE
rzeczywistości może być szybszy przy małych stołach).
W przypadku dużych tabel regularnie szybciej usuwa się indeksy i klucze obce, uzupełnia tabelę i ponownie tworzy te obiekty. Jeśli chodzi o ograniczenia fk, musisz oczywiście mieć pewność, że nowe dane są prawidłowe, w przeciwnym razie napotkasz wyjątek podczas próby utworzenia fk.
Należy pamiętać, że TRUNCATE
wymaga bardziej agresywnego blokowania niż DELETE
. Może to być problem w przypadku tabel z dużym, równoczesnym obciążeniem.
Jeśli TRUNCATE
nie jest to opcja lub ogólnie w przypadku małych i średnich tabel, istnieje podobna technika z modyfikacją danych CTE (Postgres 9.1 +):
WITH del AS (DELETE FROM tbl RETURNING *)
INSERT INTO tbl
SELECT DISTINCT * FROM del;
Wolniej przy dużych stołach, bo TRUNCATE
tam jest szybciej. Ale może być szybszy (i prostszy!) Dla małych stołów.
Jeśli nie masz żadnych obiektów zależnych, możesz utworzyć nową tabelę i usunąć starą, ale prawie nic nie zyskujesz dzięki temu uniwersalnemu podejściu.
W przypadku bardzo dużych tabel, które nie mieszczą się w dostępnej pamięci RAM , tworzenie nowej tabeli będzie znacznie szybsze. Będziesz musiał rozważyć to z możliwymi problemami / kosztami związanymi z zależnymi obiektami.