Indeks i wydajność wielokolumna

Mam tabelę z indeksem wielokolumnowym i mam wątpliwości co do właściwego sortowania indeksów, aby uzyskać maksymalną wydajność zapytań.

Scenariusz:

• PostgreSQL 8.4, tabela z około milionem wierszy

• Wartości w kolumnie c1 mogą mieć około 100 różnych wartości . Możemy założyć, że wartości są równomiernie rozłożone, więc mamy około 10000 wierszy na każdą możliwą wartość.

• Kolumna c2 może mieć 1000 różnych wartości . Mamy 1000 wierszy dla każdej możliwej wartości.

Podczas wyszukiwania danych warunek zawsze zawiera wartości dla tych dwóch kolumn, więc tabela ma indeks wielokolumnowy łączący c1 i c2. Czytałem o tym, jak ważne jest prawidłowe uporządkowanie kolumn w indeksie wielokolumnowym, jeśli masz zapytania wykorzystujące tylko jedną kolumnę do filtrowania. W naszym scenariuszu tak nie jest.

Moje pytanie brzmi:

Biorąc pod uwagę fakt, że jeden z filtrów wybiera znacznie mniejszy zestaw danych, czy mogę poprawić wydajność, jeśli pierwszy indeks jest najbardziej selektywny (ten, który pozwala na mniejszy zestaw)? Nigdy nie zastanawiałem się nad tym pytaniem, dopóki nie zobaczyłem grafiki z przywoływanego artykułu:

Zdjęcie pochodzi z przywoływanego artykułu o indeksach wielokolumnowych .

W zapytaniach do filtrowania są używane wartości z dwóch kolumn. Nie mam zapytań używających tylko jednej kolumny do filtrowania. Wszystkie z nich są: WHERE c1=@ParameterA AND c2=@ParameterB. Istnieją również takie warunki:WHERE c1 = "abc" AND c2 LIKE "ab%"

Odpowiedź

Ponieważ odwołujesz się do witryny use-the-index-luke.com, rozważ rozdział:

Użyj indeksu, Łukasz ›Klauzula dokąd› Wyszukiwanie zakresów › Większy, mniejszy i MIĘDZY

Ma przykład, który idealnie pasuje do Twojej sytuacji (indeks dwukolumnowy, jeden jest testowany pod kątem równości , drugi dla zasięgu ), wyjaśnia (z większą ilością tych ładnych grafik indeksu), dlaczego porady @ ypercube są dokładne i podsumowuje:

Rule of thumb: index for equality first — then for ranges.

Nadaje się również tylko dla jednej kolumny?

Wydaje się jasne, co zrobić w przypadku zapytań tylko w jednej kolumnie . Więcej szczegółów i punktów odniesienia dotyczących tego w ramach tego powiązanego pytania:

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

Najpierw mniej selektywna kolumna?

Poza tym, co jeśli masz tylko warunki równości dla obu kolumn ?

To nie ma znaczenia . Najpierw umieść kolumnę, która prawdopodobnie otrzyma własne warunki, co w rzeczywistości ma znaczenie.

Rozważ to demo lub sam je odtworz. Tworzę prostą tabelę złożoną z dwóch kolumn o 100 tys. Wierszy. Jeden z bardzo nielicznymi , drugi z wieloma odrębnymi wartościami:

CREATE TEMP TABLE t AS
SELECT (random() * 10000)::int AS lots
     , (random() * 4)::int     AS few
FROM generate_series (1, 100000);

DELETE FROM t WHERE random() > 0.9;  -- create some dead tuples, more "real-life"

ANALYZE t;

SELECT count(distinct lots)   -- 9999
     , count(distinct few)    --    5
FROM   t;

Pytanie:

SELECT *
FROM   t
WHERE  lots = 2345
AND    few = 2;

EXPLAIN ANALYZE wynik (najlepszy z 10, aby wykluczyć efekty buforowania):

Seq Scan on t (cost = 0,00..5840,84 wierszy = 2 szerokości = 8)
               (rzeczywisty czas = 5.646..15.535 wierszy = 2 pętle = 1)
  Filtr: ((dużo = 2345) ORAZ (kilka = 2))
  Bufory: lokalne trafienie = 443
Całkowity czas działania: 15,557 ms

Dodaj indeks, przetestuj ponownie:

CREATE INDEX t_lf_idx ON t(lots, few);

Skanowanie indeksu za pomocą t_lf_idx na t (koszt = 0,00..3,76 wierszy = 2 szerokość = 8)
                                (rzeczywisty czas = 0,008..0,011 wierszy = 2 pętle = 1)
  Indeks Warunek: ((wiele = 2345) ORAZ (kilka = 2))
  Bufory: lokalne trafienie = 4
Całkowity czas działania: 0,027 ms

Dodaj inny indeks, przetestuj ponownie:

DROP INDEX t_lf_idx;
CREATE INDEX t_fl_idx  ON t(few, lots);

Indeksuj skanowanie za pomocą t_fl_idx na t (koszt = 0,00..3,74 wierszy = 2 szerokości = 8)
                                (rzeczywisty czas = 0,007..0,011 wierszy = 2 pętle = 1)
  Indeks Cond: ((kilka = 2) ORAZ (wiele = 2345))
  Bufory: lokalne trafienie = 4
Całkowity czas działania: 0,027 ms

Jeśli, jak mówisz, zapytania dotyczące tych 2 kolumn są sprawdzeniami równości obu kolumn, np .:

WHERE c1=@ParameterA AND c2=@ParameterB

nie zawracaj sobie tym głowy. Wątpię, czy będzie jakakolwiek różnica, a jeśli będzie, to będzie ona nieistotna. Zawsze możesz oczywiście przetestować swoje dane i ustawienia serwera. Różne wersje DBMS mogą zachowywać się nieco inaczej w zakresie optymalizacji.

Kolejność w indeksie miałaby znaczenie dla innych typów zapytań, sprawdzania tylko jednej kolumny lub warunków nierówności lub warunków dla jednej kolumny i grupowania w drugiej itd.

Gdybym miał wybrać jedno z dwóch zamówień, wybrałbym najpierw kolumnę mniej selektywną. Rozważ tabelę z kolumnami yeari month. Bardziej prawdopodobne jest, że potrzebujesz WHERE year = 2000warunku lub a WHERE year BETWEEN 2000 AND 2013lub a WHERE (year, month) BETWEEN (1999, 6) AND (2000, 5).

Zapytanie tego typu WHERE month = 7 GROUP BY yearmoże być potrzebne (Znajdź osoby urodzone w lipcu), ale byłoby rzadziej. Zależy to oczywiście od rzeczywistych danych przechowywanych w tabeli. Wybierz na razie jedno zamówienie, powiedz, (c1, c2)a zawsze możesz później dodać kolejny indeks (c2, c1).

Zaktualizuj, po komentarzu PO:

Istnieją również takie warunki: WHERE c1 = 'abc' AND c2 LIKE 'ab%'

Ten typ zapytania jest dokładnie warunkiem zakresu w c2kolumnie i wymagałby (c1, c2)indeksu. Jeśli masz również zapytania typu odwrotnego:

WHERE c2 = 'abc' AND c1 LIKE 'ab%'

byłoby dobrze, gdybyś miał również (c2, c1)indeks.