Poprawa wydajności dla prostego łączenia lewostronnego w postgreSQL
Próbuję wykonać lewe sprzężenie między dwiema tabelami w bazie danych postgreSQL i stwierdzam, że uruchomienie zajmuje około 14 minut. Z istniejących postów SO wydaje się, że ten typ złączenia powinien być rzędu sekund, więc chciałbym wiedzieć, jak poprawić wydajność tego łączenia. Używam
64-bitowego
postgreSQL w wersji 9.4.4na komputerze z
Windows 8z
8 GB RAMprzy użyciu
pgAdmin III. Struktura tabeli wygląda następująco:
Tabela A: „parcels_qtr”:
pakiet (tekst) | rok (int) | kwartał (tekst) | lpid (pc, tekst) |
Ma 15,5 miliona wierszy, każda kolumna jest indeksowana, a „lpid” jest kluczem podstawowym. Przeprowadziłem również ten stół przez standardowy proces próżniowy.
Tabela B: „postalvac_qtr”:
pakiet (tekst) | rok (int) | kwartał (tekst) | lpid (pc, tekst) | vacCountY (int) |
Ma 618 000 rekordów, wszystkie pola z wyjątkiem „vacCountY” są indeksowane, a „lpid” jest kluczem podstawowym. Przeszedł również standardowy proces próżniowy.
Podczas pracy z danymi wyjściowymi zajmuje to około 14 minut. Z
wyjaśnij (analizuj, bufory)zajmuje to trochę ponad minutę. Pierwsze pytanie brzmi - czy ta różnica w wydajności wynika wyłącznie z drukowania danych, czy też dzieje się coś innego?
I drugie pytanie: czy mogę skrócić ten czas wykonania do kilku sekund?
Oto mój kod SQL:
EXPLAIN (ANALYZE, BUFFERS)
select a.parcel,
a.lpid,
a.yr,
a.qtr,
b."vacCountY"
from parcels_qtr as a
left join postalvac_qtr as b
on a.lpid = b.lpid;
A oto wyniki mojego wyjaśnienia:
https://explain.depesz.com/s/uKkK
https://explain.depesz.com/s/uKkK
Jestem całkiem nowy w PostgreSQL, więc cierpliwość i wyjaśnienie byłyby bardzo mile widziane!
Nie znaleziono powiązanych wyników
Zaproszony:
Aby odpowiedzieć na pytania, Zaloguj się lub Zarejestruj się
1 odpowiedzi
Anonimowy użytkownik
Potwierdzenie od:
Jak duże są te stoły? Możesz to sprawdzić, wydając:
SELECT pg_size_pretty(pg_relation_size('parcels_qtr'));Jestem prawie pewien, że to połączenie mieszające wylewa się na dysk i sposób, w jaki jest skonstruowane („daj mi
wszystko
dane z obu tych tabel ”), nie może być inaczej.
Indeksy nie pomagają i nie mogą. Dopóki pytasz o całą tabelę, użycie indeksu tylko spowolni działanie - postgres nadal musi przejść przez całą tabelę, więc może również wykonać skanowanie sekwencyjne.
Co do tego, dlaczego zapytanie ma inną wydajność niż, podejrzewam, że masz rację. Połączenie 1 wysyłania 15 mln wierszy do klienta i 2 prób ich wyświetlenia spowoduje znaczne spowolnienie wykraczające poza rzeczywiste żądanie.
Więc co możesz z tym zrobić?
Po pierwsze, co próbuje zrobić ta prośba? Jak często chcesz robić zdjęcia
wszystko
dane w tych dwóch tabelach są całkowicie niefiltrowane? Jeśli jest to bardzo częste, możesz rozważyć powrót do etapu wymagań i znalezienie innego sposobu spełnienia tej potrzeby (na przykład, czy mądrze byłoby zamiast tego pobrać wszystkie dane z danego roku i kwartału?). Jeśli jest to niezwykłe (powiedzmy codzienny eksport), 1-14 minut może być w porządku.
Po drugie, musisz upewnić się, że twoje stoły nie są nadęte. Jeśli napotykasz znaczny ruch związany zlubna swoich tabelach, może to z czasem wzrosnąć. Demon autoodkurzania istnieje, aby w tym pomóc, ale czasami pomaga również zwolnienie.
Po trzecie, możesz spróbować dostosować konfigurację bazy danych.zawiera opcje dotyczące rzeczy, takich jak oczekiwana ilość pamięci RAM, którą serwer może wykorzystać jako pamięć podręczną dysku oraz ilość pamięci RAM, której serwer może użyć do sortowania lub scalania (zanim rozleje się na dysk). Pracując z tymi parametrami, możesz poprawić prędkość.
Po czwarte, możesz zmienić swój schemat. Czy chcesz, aby rok i kwartał były dwiema oddzielnymi kolumnami, czy lepiej jest użyć jednej kolumny? Potrzebujesz klucza, czy lepiej jest użyć(seryjnego lub pochodzącego z kolumny), który najprawdopodobniej połączy się szybciej? Czy pola,isą wymagane w obu tabelach, czy też powielają dane w tej samej tabeli?
W każdym razie mam nadzieję, że to pomoże.