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

Powiedzmy, że mam stolik z polami Ai B. Robię regularne zapytania na A+ B, więc utworzyłem indeks złożony (A,B). Czy zapytania dotyczące Abyłyby również w pełni zoptymalizowane przez indeks złożony?

Dodatkowo utworzyłem indeks A, ale Postgres nadal używa indeksu złożonego tylko do zapytań A. Jeśli poprzednia odpowiedź jest pozytywna, to chyba nie ma to znaczenia, ale dlaczego domyślnie wybiera indeks złożony, jeśli pojedynczy Aindeks jest dostępny?

To z pewnością jest. Omówiliśmy to szczegółowo w powiązanym pytaniu:

• Działanie indeksów w PostgreSQL

Miejsce jest przydzielane w wielokrotnościach MAXALIGN, co zwykle wynosi 8 bajtów w 64-bitowym systemie operacyjnym lub (znacznie rzadziej) 4 bajty w 32-bitowym systemie operacyjnym. Jeśli nie jesteś pewien, sprawdź pg_controldata. Zależy to również od typów danych indeksowanych kolumn (niektóre wymagają wypełnienia wyrównania) i rzeczywistej zawartości.

Indeks, powiedzmy, dwóch integerkolumn (po 4 bajty) zwykle kończy się dokładnie tak samo, jak indeks tylko na jednej, gdzie kolejne 4 bajty są tracone na wypełnienie wyrównania.

W takim przypadku narzędzie do planowania zapytań nie ma tak naprawdę żadnego minusu (a,b)- w porównaniu z indeksem just (a). I generalnie zaleca się, aby wiele zapytań używało tego samego indeksu. Szansa (lub jego części) przebywania w (szybkiej) pamięci podręcznej rośnie po udostępnieniu.

Jeśli indeks jest już włączony (a,b), nie ma sensu tworzenie kolejnego indeksu tylko (a)- chyba że jest znacznie mniejszy. Tak samo jest nie prawdą dla (b,a)vs. (a). Kliknij link w pierwszym wierszu, aby uzyskać więcej informacji na ten temat.

Wychodząc z przeciwnego kierunku, kiedy potrzebujesz dodatkowego indeksu (a,b), rozważ rozważ usunięcie istniejącego indeksu tylko (a)- jeśli to możliwe. Często nie jest to możliwe, ponieważ jest to indeks PK lub UNIQUEograniczenie. Od Postgres 11 możesz uniknąć dodania bdo definicji ograniczenia z INCLUDEklauzulą. Szczegóły w instrukcji.

Lub utwórz nowy indeks (b,a)zamiast, aby dodatkowo uwzględnić zapytania b. Tylko w przypadku warunków równości kolejność wyrażeń indeksowych w indeksach btree nie ma znaczenia. Dzieje się tak jednak w przypadku warunków zasięgu. Widzieć:

• Indeks i wydajność wielokolumna

Istnieją potencjalne wady włączenia dodatkowych kolumn do indeksu, nawet jeśli wykorzystuje to tylko miejsce, które w przeciwnym razie zostałoby utracone przez wypełnienie wyrównania:

• Za każdym razem, gdy dodatkowa kolumna jest aktualizowana, indeks również potrzebuje teraz aktualizacji, co może zwiększać koszty operacji zapisu i tworzyć więcej wzdęć indeksu.
• HOT aktualizacje (Heap Only Tuple) w tabeli nie są możliwe, gdy zaangażowana jest jakakolwiek kolumna indeksu.

Więcej informacji o aktualizacjach HOT:

• Nadmiarowe dane w instrukcjach aktualizacji

Jak mierzyć rozmiary obiektów:

• Zmierz rozmiar wiersza tabeli PostgreSQL

Zgodnie z pytaniem masz tabelę z polami A i B. Jeśli twoje zapytanie brzmi:

SELECT * FROM [YOUR TBL]
WHERE A='XXXX'

Optymalizator wybierze indeks złożony, aby uniknąć wyodrębnienia losowego dostępu!

Jest tak w przypadku, gdy po prostu użyjesz najpierw w predykacie.

Wykona skanowanie, jeśli użyjesz pierwszych kolumn klucza złożonego i niekluczowej kolumny klucza złożonego.

Aby go oszukać, możesz po prostu użyć takich predykatów, a następnie niekluczowej kolumny:

[A, B] to Twój indeks, [C] - kolejna kolumna

Aby wykorzystać indeks, piszesz jako:

SELECT
    A,B,C,D,E
FROM 
    test
WHERE
   A=1
AND
   B=B
AND 
   C=3

... dlaczego domyślnie wybiera indeks złożony, jeśli dostępny jest pojedynczy indeks A?

Użyje indeksu tylko w przypadku, gdy istnieje jeden lub dwa predykaty [A] Lub [A], [B]. Nie będzie go używać w kolejności [B], [A] lub [A], [C]. Aby móc korzystać z indeksu z dodatkową kolumną [C], należy wymusić indeks, zamawiając predykaty jako [A], [B] i [C].