Odpowiedź na pytanie
PostgreSQL utrzymuje histogramy dla każdej kolumny w pg_statistic i zakłada statystyczną niezależność między kolumnami przy szacowaniu selektywności dla filtrów wielokolumnowych. Gdy filtrujesz na wysoko skorelowanych kolumnach—takich jak miasto i kodeks_stanu lub marka i model w bazie danych pojazdów—planista mnoży indywidualne selektywności, drastycznie niedoszacowując liczbę rzeczywistych wierszy. Ten błąd kardy zaliczania często powoduje, że optymalizator wybiera łączniki zagnieżdżone zamiast łączników haszujących lub łączników scalających, co prowadzi do katastrofalnej wydajności na dużych tabelach.
Aby rozwiązać ten problem, tworzysz obiekt statystyk rozszerzonych za pomocą CREATE STATISTICS, który buduje dane o korelacji wielozmiennej. Konkretnie, typ dependencies śledzi zależności funkcjonalne między kolumnami, co pozwala planisty zrozumieć, że filtracja po kodeks_stanu = 'CA' już ogranicza miasto do wartości kalifornijskich, unikając błędu mnożenia.
-- Utwórz statystyki rozszerzone dla skorelowanych kolumn CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- Zaktualizuj statystyki ANALYZE vehicles;
Sytuacja z życia
Platforma logistyczna miała problemy z panelem do śledzenia przesyłek, który łączył tabelę przesyłki z 50 milionami wierszy z klientami. Zapytanie filtrowało po origin_state i origin_city, gdzie 95% wierszy dla 'Springfield' jako miasta znajdowało się w 'IL', ale planista założył, że tylko 2% przesyłek spełnia oba predykaty niezależnie. Oszacował 500 wierszy i wybrał łącznik zagnieżdżony, przechodząc przez miliony rekordów klientów i unosząc przez 90 sekund.
Jednym z rozważanych rozwiązań było całkowite wyłączenie łączników zagnieżdżonych poprzez SET enable_nestloop = off w sesji. To wymusiło łącznik haszujący i zakończyło się w 3 sekundy dla tego konkretnego zapytania, ale niosło ze sobą poważne ryzyko: globalne zmiany konfiguracyjne propagują się przez pule połączeń, mogąc regresować inne uzasadnione plany łączników zagnieżdżonych, które dobrze wykonują się na małych tabelach z wyszukiwaniami indeksowymi. Dodatkowo, ta prowizoryczna poprawka wymagała zmian w kodzie na poziomie aplikacji, aby ustawić parametr przed zapytaniem.
Inna opcja polegała na utworzeniu indeksu złożonego na (origin_city, origin_state). Choć to poprawiło wybór indeksów, nie rozwiązało problemu niedoszacowania kardy. Planista nadal myślał, że niewiele wierszy wyłoni się z skanu indeksu i utrzymywał strategię łącznika zagnieżdżonego, tylko wykonując ją szybciej przez indeks pokrywający. Ponadto, szeroki indeks złożony zajmował dodatkowe 4 GB przestrzeni dyskowej i spowalniał operacje zapisu w szybko zmieniającej się tabeli przesyłki.
Zespół ostatecznie wdrożył statystyki rozszerzone, uruchamiając CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, a następnie ANALYZE. To podejście nie wymagało przeróbek zapytań i dodało nieznaczny koszt przechowywania. Po wdrożeniu planista prawidłowo oszacował 45 000 wierszy i wybrał łącznik haszujący, zmniejszając latencję zapytania do 400 milisekund, jednocześnie zachowując optymalne plany dla niepowiązanych obciążeń.
Czego często brakuje kandydatom
Jak polecenie ANALYZE odświeża statystyki rozszerzone i dlaczego obiekt statystyczny może wydawać się nieużywany natychmiast po utworzeniu?
ANALYZE oblicza statystyki rozszerzone tylko wtedy, gdy jest uruchamiane jawnie na docelowej tabeli lub gdy procesy autovacuum przetwarzają tabelę po utworzeniu obiektu statystycznego. Wielu kandydatów zakłada, że CREATE STATISTICS natychmiastowo wpływa na planowanie, ale tabele katalogowe pg_statistic_ext i pg_statistic_ext_data pozostają puste aż do następnej analizy. W związku z tym planista nadal używa histogramów dla pojedynczej kolumny i założeń o niezależności, dopóki ANALYZE shipments; nie zapełni danych wielozmiennych. Możesz zweryfikować użycie, sprawdzając widok pg_stats_ext pod kątem niepustych wartości dependencies lub ndistinct.
Jaka jest funkcjonalna różnica między dependencies a ndistinct w CREATE STATISTICS, i które wzorce zapytań korzystają z każdego?
Zależności uchwycają relacje funkcjonalne, w których jedna kolumna określa drugą (np., zip_code określa miasto), bezpośrednio korygując oszacowania selektywności klauzuli WHERE. Ndistinct oblicza dokładną liczbę unikalnych kombinacji dla grup kolumn, co poprawia oszacowania GROUP BY i DISTINCT, a nie selektywność filtrów. Kandydaci często mylą te dwa pojęcia, tworząc dependencies, gdy ich wolne zapytanie zawiera GROUP BY na skorelowanych kolumnach, lub odwrotnie. Dla najlepszych rezultatów, określ oba typy: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
Dlaczego statystyki rozszerzone mogą nie pomóc w zapytaniach używających warunków OR na skorelowanych kolumnach?
Statystyki rozszerzone obecnie wspierają tylko klauzule AND, gdzie następuje mnożenie selektywności. Kiedy filtrujesz za pomocą OR (np., miasto = 'Springfield' OR stan = 'IL'), PostgreSQL oblicza selektywność, używając wzoru P(A) + P(B) - P(A ∩ B), i nie może zastosować współczynników zależności do terminu przecięcia, ponieważ statystyki śledzą wspólną selektywność dla koniunkcji, a nie alternacji. Kandydaci często pomijają to ograniczenie i próbują używać CREATE STATISTICS do naprawy błędów kardynalnych z użyciem OR, co wymaga przeróbki zapytań (np., podzielenie na gałęzie UNION ALL) lub zamiast tego zastosowania indeksów częściowych.