Czy istnieje sposób, aby znaleźć najlepszy sposób, aby dowiedzieć się, które indeksy utworzyć dla tabeli?

Krótkie zasady praktyczne. (Niektóre z nich są tworzone automatycznie, ale można je później usunąć ręcznie, w zależności od dbms. Nie zakładaj, że zawsze będziesz pracować na PostgreSQL.)

• Indeksuj każdy klucz podstawowy.
• Indeksuj każdy klucz obcy.
• Indeksuj każdą kolumnę używaną w klauzuli JOIN.
• Indeksuj każdą kolumnę używaną w klauzuli WHERE.
• Zapoznaj się z dokumentacją, aby poznać „ezoteryczne” opcje indeksowania obsługiwane przez dbms.

Każdy klucz podstawowy oznacza, że ​​klucze główne z wieloma kolumnami powinny mieć jeden indeks obejmujący wszystkie kolumny. PostgreSQL utworzy ten indeks automatycznie, jeśli zadeklarujesz wielokolumnowy klucz podstawowy.

Istnieje wiele przypadków, w których pojedynczy indeks wielokolumnowy zapewnia lepszą wydajność niż kilka indeksów jednokolumnowych. Monitoruj wolne zapytania i wykonuj testy, aby dowiedzieć się, który jest który.

Załóżmy, że każda zmiana w indeksowaniu poprawi niektóre działania bazy danych i pogorszy inne. Pomocne jest posiadanie zestawu instrukcji SQL, które mogę profilować przed i po wprowadzeniu zmian w indeksach. Ten zestaw zawiera instrukcje SELECT, INSERT, UPDATE i DELETE.

Nie ma substytutu dla studiowania dokumentów dla poszczególnych dbms.

• UTWÓRZ INDEKS
• Indeksy (zwróć uwagę zwłaszcza na sekcje dotyczące indeksowania wyrażeń, indeksów częściowych i badania użycia indeksu)

Oprócz tego, co @Catcall już podał , i aby dodać małą poprawkę:

Omówiłem także kilka podstaw w tej ściśle powiązanej odpowiedzi dotyczącej SO .

Dotychczasowe odpowiedzi wydają się wskazywać, że musisz utworzyć indeksy na kluczach podstawowych, ale nie jest tak w PostgreSQL (obowiązują częściowe wyjątki). Przytaczam instrukcję tutaj :

PostgreSQL automatycznie tworzy unikalny indeks, gdy dla tabeli zdefiniowane zostanie unikalne ograniczenie lub klucz podstawowy. Indeks obejmuje kolumny, które tworzą klucz podstawowy lub ograniczenie przez unikalność (indeks wielokolumnowej, w razie potrzeby), i jest to mechanizm, który wymusza ograniczenie.

Odważny nacisk moje.

Ty może chcesz tworzyć dodatkowych indeksów dla drugiego lub kolejnych kolumnach indeksu wielokolumnowego, ale pierwszy jest ogólnie dobrze pokryte przez indeks wielokolumnowego - z wyjątkiem gdy dodatkowe kolumny zrobić indeks znacznie większy. Omówiliśmy to szczegółowo w powiązanym pytaniu:

Czy indeks złożony jest również przydatny w przypadku zapytań dotyczących pierwszego pola?

Wielokolumnowe indeksy , częściowe indeksy i indeksy na wyrażeniach są szczególnie potężne narzędzia w PostgreSQL. Od wersji PostgreSQL 9.2 dostępne są również skany tylko indeksowe , co odpowiada „indeksom pokrywającym” w innych RDBMS. To nie jest inny typ indeksu, ale nowa funkcja RDBMS z istniejącymi typami indeksu.

Każdy indeks wiąże się z określonymi kosztami , więc nie ma możliwości oparcia się na podstawowej wiedzy, aby naprawdę zoptymalizować indeksowanie. Samo utworzenie większej liczby indeksów może przynieść więcej szkody niż pożytku. W szczególności indeksy mogą uniemożliwić poprawienie wydajności aktualizacji HOT .

Ogólnie rzecz biorąc, operacje zapisu ( DELETE, UPDATE) stają się droższe (ale mogą również przynieść korzyści!), Podczas gdy operacje odczytu ( SELECT) generalnie po prostu korzystają. Zbyt wiele indeksów może wyczerpać pamięć podręczną, przez co mogą ucierpieć nawet operacje odczytu .

Wreszcie, ta strona Wiki Postgres na temat konserwacji indeksu zawiera narzędzia do znajdowania zduplikowanych lub nieużywanych indeksów (między innymi).

Istnieją dwie opcje.

1. Ty to zrób.
2. Technologia to robi.

Odpowiedź na to, że musisz to zrobić sam, jest dość wyczerpująco udokumentowana tutaj. Spójrzmy więc na coś innego.

Pghero

Pghero może ci pomóc, jeśli potrzebujesz automatycznych porad.

Powiedział, że ma pewne wady.

1. Działa tylko WHEREi ORDER BYnie JOINS.
2. Wykorzystuje tylko statystyki dotyczące wartości procentowej NULL i różne wartości.

Sprawdź ten film, aby uzyskać więcej informacji .