Historia: PostgreSQL wykorzystuje optymalizator oparty na kosztach, który przypisuje abstrakcyjne jednostki monetarne operacjom I/O. Wczesne systemy baz danych były głównie ukierunkowane na talerze magnetyczne, gdzie opóźnienia w wyszukiwaniu czyniły odczyt losowy około 40 razy droższym niż odczyt sekwencyjny. Aby złagodzić tę asymetrię, wprowadzono Bitmap Index Scans, aby zrównoważyć losowe pobieranie stron poprzez skonstruowanie bitmapy lokalizacji dopasowanych krotek i dostęp do stosu w przybliżonej kolejności fizycznej.
Problem: Kluczowy dylemat występuje przy filtrowaniu średnio selektywnych predykatów, które pasują do tysięcy wierszy rozproszonych po wielu stronach danych. Index Scan wykonuje jedno losowe I/O dla każdego wskaźnika dopasowanej krotki, co powoduje trzepotanie dysku mechanicznego lub nadmierne żądania I/O na SSD. Z drugiej strony, Bitmap Index Scan wiąże się z dodatkowym kosztem budowy struktury bitmapy i może przetwarzać nieistotne wiersze, jeśli bitmapa stanie się stratna z powodu ograniczeń work_mem.
Rozwiązanie: Próg decyzyjny znajduje się w funkcjach cost_index() i cost_bitmap_heap_scan(). Planista szacuje liczbę odrębnych stron stosu (pages_fetched) wymaganych do zaspokojenia zapytania. Gdy pages_fetched przekracza stosunek random_page_cost / seq_page_cost, optymalizator preferuje podejście bitmapowe, ponieważ koszt pobierania posortowanych stron przewyższa karę za losowy dostęp. Obniżenie random_page_cost (np. z 4.0 do 1.1 dla pamięci SSD) zmniejsza postrzeganą karę za losowe I/O, skłaniając planistę z powrotem w kierunku standardowych Index Scans dla selektywności, które wcześniej uruchamiały tworzenie bitmapy.
Platforma do raportowania finansowego zmagała się z poważnymi opóźnieniami na zapytaniu dashboardowym agregującym transactions według account_id za bieżący kwartał fiskalny. Tabela zawierała 500 milionów wierszy na przestarzałym SAN z talerzami mechanicznymi. Predykat account_id = 12345 pasował do około 12% wierszy rozproszonych losowo w całym stosie. Plan wykonania ujawnił standardowy Index Scan trwający 14 sekund z powodu burz losowego I/O na tysiącach stron liści.
Zwiększenie random_page_cost z 4.0 do 8.0 jednoznacznie sygnalizowało optymalizatorowi, że losowe wyszukiwania na dysku są nieproporcjonalnie drogie. Ta natychmiastowa zmiana zmusiła planistę do wyboru Bitmap Index Scan, co zmniejszyło czas wykonania do 1.8 sekundy poprzez grupowanie żądań stron w posortowane zakresy. Jednak ta globalna konfiguracja ukarała zapytania punktowe OLTP w innym miejscu w aplikacji, powodując, że przeszły na mniej efektywne skanowania sekwencyjne, które zwiększyły kontencję blokad podczas godzin szczytu handlowego.
Utworzenie indeksu przykrywającego na (account_id, transaction_date, amount) umożliwiło Index Only Scan, który całkowicie omijał stos, uzyskując czasy odpowiedzi wynoszące 80 ms. Choć optymalne dla odczytów, złożony indeks zwiększył rozmiar tabeli o 35% i zmniejszył przepustowość wstawiania o 22%, ponieważ każdy wstawiony wiersz wymagał teraz utrzymywania dwóch dużych struktur B-drzew, łamiąc surowe SLA dotyczące rejestrowania transakcji w czasie rzeczywistym.
Postanowiliśmy wdrożyć partycjonowanie tabeli według zakresu na created_at w połączeniu z umiarkowaną random_page_cost wynoszącą 6.0. To hybrydowe podejście ograniczyło zapytanie do partycji bieżącego kwartału, zmniejszając całkowitą liczbę stron poniżej progu bitmapy, podczas gdy podwyższony parametr kosztów zapewnił, że zapytania historyczne między partiami nadal wykorzystywały bitmapy, aby zapobiec nasyceniu losowym I/O. To rozwiązanie uwzględniło ograniczenia wydajności zapisu systemu handlowego, jednocześnie optymalizując ścieżkę raportowania o dużej liczbie odczytów.
Rezultat: Zapytanie dashboardowe ustabilizowało się na poziomie 400 ms bez pogarszania wydajności wstawiania OLTP, a wykorzystanie I/O dysku na węźle raportowania spadło z 95% do 30% w godzinach pracy.
W jaki sposób effective_cache_size interaguje z random_page_cost w modelu kosztów planisty i dlaczego obniżenie random_page_cost w systemie z dużą pamięcią podręczną może w rzeczywistości pogorszyć wydajność dla niektórych typów złączeń?
effective_cache_size mierzy dostępność pamięci na buforowanie dysku. Gdy jest ustawione wysoko, planista zakłada, że wiele stron znajduje się w RAM, efektywnie pomijając koszty I/O niezależnie od ustawienia random_page_cost. Jeśli agresywnie obniżysz random_page_cost do 1.1 (typowe dla SSD NVMe) przy zachowaniu dużego effective_cache_size, optymalizator może nieracjonalnie preferować złącza Nested Loop z użyciem Index Scans zamiast Hash Joins. Model zakłada, że próby indeksu wewnętrznego są prawie darmowe, ponieważ losowe I/O jest tanie i buforowane, ignorując, że masowe pętle wewnętrzne wciąż nasycają CPU przetwarzaniem krotek i powodują usunięcie z pamięci podręcznej, co prowadzi do gorszego czasu rzeczywistego niż pojedyncza operacja haszowania, która skanuje wewnętrzną tabelę raz.
W jaki sposób Bitmap Index Scan w PostgreSQL różni się od Bitmap Heap Scan, i dlaczego planista wybiera BitmapOr operacje w wielu indeksach zamiast używać jednego indeksu złożonego?
Bitmap Index Scan przeszukuje strukturę indeksu, aby skonstruować bitmapę wskaźników dopasowanych krotek (lub zakresów stron, jeśli jest stratna). Bitmap Heap Scan następnie pobiera rzeczywiste dane wierszy z tabeli, używając tej bitmapy do sekwencyjnego dostępu do stron. BitmapOr (lub BitmapAnd) występuje, gdy zapytanie filtruje na warunkach takich jak WHERE status = 'active' OR priority = 'high', pasując do osobnych indeksów. Ponieważ PostgreSQL nie może jednocześnie przeszukiwać dwóch B-drzew efektywnie w jednym przebiegu, generuje bitmapy z każdego indeksu niezależnie i łączy je za pomocą operacji bitowych. Ta technika jest preferowana w porównaniu do indeksu złożonego (status, priority), gdy zapytania filtrują tylko po status, tylko po priority lub obu zmiennie, ponieważ utrzymywanie dwóch oddzielnych indeksów powoduje znacznie mniejsze zwiększenie zapisów niż wiele pokrywających złożonych wariantów.
Kiedy zapytanie używa klauzuli LIMIT, dlaczego PostgreSQL wciąż może wybierać Bitmap Index Scan pomimo preferencji standardowego Index Scan z powodu wczesnego zakończenia, i w jaki sposób nieaktualne statystyki wpływają na tę błędną kalkulację?
Standardowy Index Scan może zakończyć wykonanie natychmiast po pobraniu LIMIT N wierszy, jeśli indeks wspiera potrzebne sortowanie, minimalizując I/O. Jednak jeśli planista niedoszacowuje liczbę wierszy spełniających predykat — z powodu nieaktualnych statystyk ANALYZE lub skorelowanych kolumn — zakłada, że Index Scan będzie przeszukiwał nadmierną liczbę stron liści przed znalezieniem dopasowań. W związku z tym wybiera Bitmap Index Scan, aby zrównoważyć koszty I/O. Ponieważ bitmapy muszą być w pełni zmaterializowane przed dostępem do stosu, wykonawca nie może zatrzymać się wcześnie; buduje bitmapę zawierającą tysiące wierszy tylko po to, by odrzucić wszystkie oprócz dziesięciu, co skutkuje katastrofalnym opóźnieniem w porównaniu do optymistycznego oszacowania planisty.