Indeks klucza podstawowego nieużywany w prostym złączeniu

Mam następujące definicje tabel i indeksów:

CREATE TABLE munkalap (
    munkalap_id serial PRIMARY KEY,
    ...
);

CREATE TABLE munkalap_lepes (
    munkalap_lepes_id serial PRIMARY KEY,
    munkalap_id integer REFERENCES munkalap (munkalap_id),
    ...
);

CREATE INDEX idx_munkalap_lepes_munkalap_id ON munkalap_lepes (munkalap_id);

Dlaczego żaden z indeksów na munkalap_id nie jest używany w następującym zapytaniu?

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id);

QUERY PLAN
Hash Join  (cost=119.17..2050.88 rows=38046 width=214) (actual time=0.824..18.011 rows=38046 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.005..4.574 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=3252 width=4) (actual time=0.810..0.810 rows=3253 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 115kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=3252 width=4) (actual time=0.003..0.398 rows=3253 loops=1)
Total runtime: 19.786 ms

To samo, nawet jeśli dodam filtr:

EXPLAIN ANALYZE SELECT ml.* FROM munkalap m JOIN munkalap_lepes ml USING (munkalap_id) WHERE NOT lezarva;

QUERY PLAN
Hash Join  (cost=79.60..1545.79 rows=1006 width=214) (actual time=0.616..10.824 rows=964 loops=1)
  Hash Cond: (ml.munkalap_id = m.munkalap_id)
  ->  Seq Scan on munkalap_lepes ml  (cost=0.00..1313.46 rows=38046 width=214) (actual time=0.007..5.061 rows=38046 loops=1)
  ->  Hash  (cost=78.52..78.52 rows=86 width=4) (actual time=0.587..0.587 rows=87 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 4kB
        ->  Seq Scan on munkalap m  (cost=0.00..78.52 rows=86 width=4) (actual time=0.014..0.560 rows=87 loops=1)
              Filter: (NOT lezarva)
Total runtime: 10.911 ms

Wiele osób słyszało wskazówki, że „sekwencyjne skany są złe” i starają się wyeliminować je ze swoich planów, ale nie jest to takie proste. Jeśli zapytanie obejmie każdy wiersz w tabeli, skanowanie sekwencyjne jest najszybszym sposobem na uzyskanie tych wierszy. Dlatego w pierwotnym zapytaniu o połączenie zastosowano skanowanie sekwencyjne, ponieważ wszystkie wiersze w obu tabelach były wymagane.

Planując zapytanie, planista Postgres szacuje koszty różnych operacji (obliczenia, sekwencyjne i losowe operacje we / wy) w ramach różnych możliwych schematów i wybiera plan, który według niego ma najniższy koszt. Podczas wykonywania operacji we / wy z rotacyjnego magazynu (dysków) losowe operacje we / wy są zwykle znacznie wolniejsze niż sekwencyjne operacje we / wy, domyślna konfiguracja pg dla random_page_cost i seq_page_cost szacuje różnicę kosztów 4: 1.

Te rozważania wchodzą w grę, gdy rozważamy metodę łączenia lub filtrowania, która wykorzystuje indeks vs indeks, który sekwencyjnie skanuje tabelę. Podczas korzystania z indeksu plan może szybko znaleźć wiersz za pomocą indeksu, a następnie musi uwzględnić losowy odczyt bloku, aby rozwiązać dane wiersza. W przypadku drugiego zapytania, które dodało predykat filtrowania WHERE NOT lezarva, możesz zobaczyć, jak wpłynęło to na oszacowania planowania w wynikach ANALIZY WYJAŚNIENIA. Planista szacuje 1006 wierszy wynikających ze złączenia (co dość ściśle odpowiada rzeczywistemu zestawowi wyników 964). Biorąc pod uwagę, że większa tabela munkalap_lepes zawiera około 38 tys. Wierszy, planista widzi, że złączenie będzie musiało uzyskać dostęp do około 1006/38046 lub 1/38 wierszy w tabeli. Wie również, że średnia szerokość wiersza wynosi 214 bajtów, a blok ma 8 KB, więc jest około 38 wierszy / blok.

Dzięki tym statystykom planista uważa za prawdopodobne, że złączenie będzie musiało odczytać wszystkie lub większość bloków danych tabeli. Ponieważ wyszukiwania indeksów również nie są bezpłatne, a obliczenia do skanowania bloku oceniającego warunek filtru są bardzo tanie w stosunku do IO, planista zdecydował się na sekwencyjne skanowanie tabeli i unikanie narzutu indeksu i losowych odczytów podczas obliczania skanu seq będzie szybciej.

W prawdziwym świecie dane są często dostępne w pamięci za pośrednictwem pamięci podręcznej stron systemu operacyjnego, dlatego nie każdy odczyt bloku wymaga operacji wejścia / wyjścia. Trudno jest przewidzieć, jak skuteczna będzie pamięć podręczna dla danego zapytania, ale planista PG korzysta z prostych heurystyk. Wartość konfiguracji efektywna wielkość_cache'u informuje planistów o prawdopodobieństwie poniesienia rzeczywistych kosztów We / Wy. Większa wartość spowoduje, że oszacuje on niższy koszt losowego IO, a tym samym może przesunąć go w kierunku metody opartej na indeksie na podstawie skanowania sekwencyjnego.

Pobierasz wszystkie wiersze z obu tabel, więc nie ma realnych korzyści z zastosowania skanowania indeksu. Skanowanie indeksu ma sens tylko wtedy, gdy wybierasz tylko kilka wierszy z tabeli (zwykle mniej niż 10% -15%)