Historia pytania
PostgreSQL wprowadził przygotowane instrukcje, aby wyeliminować koszty analizy i planowania dla wielokrotnie wykonywanych zapytań SQL. Wczesne wersje zawsze generowały niestandardowe plany wykonania dostosowane do konkretnych wartości parametrów, ale powodowało to znaczne koszty CPU dla złożonych zapytań. Aby to zoptymalizować, w wersji PostgreSQL 9.2 wprowadzono pamięć podręczną planów z mechanizmem planu ogólnego, który ponownie wykorzystuje jedną strukturę planu w wielu wykonaniach. Choć to podejście zmniejsza opóźnienie planowania, traktuje wszystkie parametry jako nieprzezroczyste miejsca zastępcze podczas początkowej fazy planowania.
Problem
Przycinanie partycji w PostgreSQL działają na dwóch wyraźnych etapach: przycinanie w czasie planowania, które występuje podczas planowania zapytania, gdy planista bada ograniczenia partycji w odniesieniu do wartości literałowych, oraz przycinanie w czasie wykonania, które filtruje partycje podczas wykonywania za pomocą joinów partycyjnych lub filtrowania węzłów dodawania. Ogólne plany generowane dla przygotowanych instrukcji nie mają konkretnych wartości parametrów w czasie planowania, co uniemożliwia przycinanie w czasie planowania. W związku z tym planista generuje skanowanie przez wszystkie partycje niezależnie od rzeczywistych wartości parametrów dostarczonych podczas wykonania, prowadząc do katastrofalnego pogorszenia wydajności na dużych tabelach partycjonowanych.
Rozwiązanie
Rozwiązaniem jest wymuszenie PostgreSQL na generowanie niestandardowych planów, które obejmują rzeczywiste wartości parametrów podczas fazy planowania. Osiąga się to poprzez ustawienie parametru konfiguracyjnego plan_cache_mode na force_custom_plan dla sesji lub konkretnego zapytania, omijając ogólną pamięć podręczną planu całkowicie. Alternatywnie, obejścia na poziomie protokołu obejmują użycie protokół zapytania rozszerzonego z wiadomością Bind zawierającą wartości literałowe zamiast parametrów, lub wykorzystywanie klientów budujących zapytania, które wstawiają literały dla kluczy partycji, jednocześnie zachowując inne parametry związane w celu zapobiegania SQL wstrzyknięciom.
-- Wymuś niestandardowy plan dla tej sesji SET plan_cache_mode = force_custom_plan; -- Lub użyj dynamicznego SQL z format() do bezpiecznego wstawiania literałów EXECUTE format('SELECT * FROM logs WHERE log_date >= %L', '2024-01-01');
Platforma analityczna wysokiej częstotliwości do handlu doświadczyła poważnych skoków opóźnienia każdego ranka podczas zapytań dotyczących intradayowych ruchów cenowych. Baza danych przechowywała dane tick w tabeli partycjonowanej według daty sesji handlowej, zawierającej ponad 2,000 partycji rozciągających się na pięć lat. Aplikacja używała przygotowanych instrukcji JDBC z ? jako miejscami zastępczymi dla parametru daty w celu zapobiegania SQL wstrzyknięciom i zmniejszenia kosztów analizy.
Zespół deweloperów początkowo zauważył, że zapytania filtrujące dane "dzisiejsze" skanowały historyczne partycje, zajmując 45 sekund zamiast oczekiwanych 300 milisekund. To pogorszenie wydajności wystąpiło, ponieważ ogólny plan nie mógł wyeliminować nieistotnych partycji podczas fazy planowania.
Jednym z podejść było stworzenie osobnej niezapisywanej tabeli dla gorących danych oraz migracja rekordów nocą. Ta strategia całkowicie ominęłaby tabelę partycjonowaną dla najnowszych zapytań, ale wprowadzała złożoną logikę ETL i narażała na utratę danych podczas awarii systemu.
Inna propozycja sugerowała, aby globalnie wyłączyć przygotowane instrukcje w puli połączeń JDBC. Choć przywróciłoby to przycinanie w czasie planowania poprzez udostępnienie wartości literałowych planistowi, testy wykazały 40% wzrost wykorzystania CPU na serwerze bazy danych z powodu powtarzających się kosztów analizy i planowania.
Zespół ocenił również użycie możliwości przycinania partycji w czasie wykonywania wprowadzonej w wersji 11 PostgreSQL. Jednak przycinanie w czasie wykonywania eliminuje partycje tylko po rozpoczęciu skanowania przez wykonawcę, co oznacza, że planista nadal przydzielał zasoby do wszystkich partycji i generował suboptymalne porządki złączeń, które ignorowały granice partycji.
Ostatecznie zespół zdecydował się na wdrożenie przełączania konfiguracji na poziomie połączenia. Skonfigurowali pulę połączeń, aby wykrywać zapytania dotyczące tabel partycjonowanych i wykonywać SET plan_cache_mode = force_custom_plan przed wysłaniem tych konkretnych instrukcji. Utrzymało to korzyści bezpieczeństwa wynikające z parametrów zapytań dla filtrów użytkownika, jednocześnie zapewniając, że wartości klucza partycji były widoczne dla planisty.
Rezultatem było zmniejszenie opóźnienia zapytań do 280 milisekund i zmniejszenie ogólnego zużycia CPU w bazie danych o 15%, ponieważ planista mógł teraz wykorzystać wykluczenie ograniczeń do wyeliminowania 1,999 partycji przed wykonaniem. Ta optymalizacja pozwoliła platformie handlowej spełnić surowe wymagania dotyczące opóźnienia każdego ranka bez kompromitowania integralności danych ani bezpieczeństwa.
Jak PostgreSQL decyduje między ogólnymi a niestandardowymi planami, gdy plan_cache_mode jest ustawione na auto?
W trybie auto PostgreSQL planuje i wykonuje zapytanie, używając niestandardowego planu przez pierwsze pięć wykonań, akumulując koszt planowania. Po piątym wykonaniu porównuje średni czas wykonania ogólnego planu (oszacowany podczas pierwszego wykonania) z średnim czasem wykonania planów niestandardowych oraz ich kosztami planowania. Jeśli oszacowany koszt ogólnego planu jest mniejszy niż średni koszt planu niestandardowego, system na stałe przełącza się na ogólny plan dla tej przygotowanej instrukcji. Kandydaci często pomijają, że to porównanie obejmuje oszczędności wynikające z ponownego wykorzystania ogólnego planu, a decyzja jest stała przez cały czas życia przygotowanej instrukcji, chyba że zostanie wyraźnie zaplanowana na nowo.
Jaka jest różnica między przycinaniem partycji w czasie planowania a przycinaniem partycji w czasie wykonywania w kontekście przygotowanych instrukcji?
Przycinanie w czasie planowania występuje w fazie planowania, gdy planista może dowieść, że niektóre partycje nie mogą zawierać istotnych wierszy na podstawie ograniczeń partycji i wartości literałowych w zapytaniu. Przycinanie w czasie wykonywania występuje podczas wykonywania, gdy wykonawca sprawdza ograniczenia partycji w odniesieniu do rzeczywistych wartości parametrów, korzystając z mechanizmu filtrowania wykonywacza. Ogólne przygotowane plany wspierają przycinanie w czasie wykonywania od PostgreSQL 11, ale nie mogą wspierać przycinania w czasie planowania, ponieważ wartości parametrów są nieznane. Kandydaci często mylą te mechanizmy, wierząc, że przycinanie w czasie wykonywania rozwiązuje wszystkie problemy z partycjami przygotowanych instrukcji, nie zdając sobie sprawy, że przycinanie w czasie planowania jest kluczowe dla efektywnego planowania złączeń i wyboru indeksów.
Dlaczego force_custom_plan może nie rozwiązać problemów z przycinaniem partycji w wersjach PostgreSQL przed 10?
Przed wersją 10 PostgreSQL nie miał istotnego wsparcia dla przycinania partycji w czasie wykonywania, a przygotowane instrukcje nie mogły korzystać z wykluczenia ograniczeń nawet z niestandardowymi planami, jeśli parametry były przekazywane przez protokół zapytania rozszerzonego za pomocą wiadomości Bind. Planista traktował wszystkie związane parametry jako zewnętrzne w procesie planowania, wymagając wyraźnych wartości literałowych w samej treści zapytania, aby wywołać wykluczenie ograniczeń. To historyczne ograniczenie oznacza, że w starszych systemach nawet niestandardowe plany skanowałyby wszystkie partycje, co wymagałoby dynamicznego generowania SQL z EXECUTE ... USING z literami lub łączenia ciągów po stronie klienta z odpowiednim zabezpieczeniem, zamiast nowoczesnego wiązania parametrów na poziomie protokołu.