Dlaczego PostgreSQL wykonuje skanowanie sekwencyjne na indeksowanej kolumnie?

150

Bardzo prosty przykład - jedna tabela, jeden indeks, jedno zapytanie:

CREATE TABLE book
(
  id bigserial NOT NULL,
  "year" integer,
  -- other columns...
);

CREATE INDEX book_year_idx ON book (year)

EXPLAIN
 SELECT *
   FROM book b
  WHERE b.year > 2009

daje mi:

Seq Scan on book b  (cost=0.00..25663.80 rows=105425 width=622)
  Filter: (year > 2009)

Dlaczego zamiast tego NIE wykonuje skanowania indeksu? czego mi brakuje?

— Alex Vayda
źródło

222

Jeśli funkcja SELECT zwraca więcej niż około 5–10% wszystkich wierszy w tabeli, skanowanie sekwencyjne jest znacznie szybsze niż skanowanie indeksu.

Dzieje się tak, ponieważ skanowanie indeksu wymaga kilku operacji we / wy dla każdego wiersza (wyszukaj wiersz w indeksie, a następnie pobierz wiersz ze sterty). Podczas gdy skanowanie sekwencyjne wymaga tylko jednego IO dla każdego wiersza - lub nawet mniej, ponieważ blok (strona) na dysku zawiera więcej niż jeden wiersz, więc więcej niż jeden wiersz można pobrać za pomocą jednej operacji IO.

Przy okazji: dotyczy to również innych DBMS - niektóre optymalizacje, takie jak „skanowanie tylko indeksów” zostały pominięte (ale w przypadku SELECT * jest bardzo mało prawdopodobne, że taki DBMS przejdzie na „skanowanie tylko indeksów”)

— koń bez imienia
źródło

12

5-10% zależy od kilku ustawień konfiguracyjnych i przechowywania danych. To nie jest trudna liczba.

— Frank Heikens

6

@Frank: dlatego powiedziałem „w przybliżeniu” :) Ale dzięki za wskazanie tego

— a_horse_with_no_name

5

Ponadto skanowanie sekwencyjne może zażądać kilku stron ze sterty na raz i poprosić jądro o pobranie następnej porcji, podczas gdy działa na bieżącej - skanowanie indeksu pobiera jedną stronę naraz. (Skanowanie mapy bitowej stanowi kompromis między nimi, zwykle widać, że pojawia się w planie zapytań, które nie są wystarczająco selektywne do skanowania indeksu, ale nadal nie są tak nieselektywne, aby zasługiwały na pełne skanowanie tabeli)

— araqnid

4

Interesujące pytanie brzmi: skąd baza danych wie, ile wierszy zwróci zapytanie bez wcześniejszego zrobienia tego? Czy gdzieś przechowuje statystyki, takie jak liczba różnych wartości i rozmiar tabeli?

— Laurent Grégoire

7

@ LaurentGrégoire: tak, baza danych przechowuje statystyki dotyczące liczby wierszy i rozkładu wartości. Szczegółowe informacje można znaleźć w instrukcji: postgresql.org/docs/current/static/planner-stats.html

— a_horse_with_no_name

13

Czy przeanalizowałeś tabelę / bazę danych? A co ze statystykami ? W przypadku wielu rekordów, w których rok> 2009, skanowanie sekwencyjne może być szybsze niż skanowanie indeksu.

— Frank Heikens
źródło

0

Podczas skanowania indeksu głowica odczytu przeskakuje z jednego wiersza do drugiego, co jest 1000 razy wolniejsze niż odczyt następnego bloku fizycznego (w skanowaniu sekwencyjnym).

Tak więc, jeśli (liczba rekordów do pobrania * 1000) jest mniejsza niż całkowita liczba rekordów, skanowanie indeksu będzie działać lepiej.

— Gaurav Neema
źródło

0

@a_horse_with_no_name wyjaśnił to całkiem dobrze. Również jeśli naprawdę chcesz użyć skanowania indeksu, powinieneś generalnie używać ograniczonych zakresów w klauzuli where. np. - rok> 2019 i rok <2020.

W wielu przypadkach statystyki dotyczące tabeli nie są aktualizowane i może nie być to możliwe z powodu ograniczeń. W takim przypadku optymalizator nie będzie wiedział, ile wierszy powinien zająć w roku> 2019. W związku z tym zamiast pełnej wiedzy wybiera skanowanie sekwencyjne. Ograniczone partycje rozwiązują problem w większości przypadków.

— Shitij Goyal
źródło