Ze względu na mocno ograniczone możliwości wpływania na sposób generowania planu wykonania zapytania przez Query Planner, wielu deweloperów wykorzystywało obce rozszerzenia do PostgreSQL, np. popularny pg_hint_plan. Ponieważ jednak jest to rozszerzenie spoza pakietu Contrib, to często pojawiały się przeszkody formalne (akceptacje działów ds bezpieczeńtwa) w ich zastosowaniach produkcyjnych. Szczęśliwie, PostgreSQL 19 wprowadza nowe rozwiązanie, w pełni pochodzące z zaufanej dystrybucji – pg_plan_advice – umożliwiające narzucanie Query Plannerowi własnych decyzji dotyczących konstrukcji planu wykonania zapytania.
Rozszerzenie pg_plan_advice musi zostać najpierw zarejestrowane na serwerze, np. za pomocą parametru konfiguracyjnego shared_preload_libraries = 'pg_plan_advice'. Od tego momentu, przed wykonaniem zapytania, programista może za pomocą specjalnej składni przekazać wskazówki Query Plannerowi. Wskazówki te zostaną (o ile są stosowalne) uwzględnione podczas generowania planu wykonania zapytania. Przyjrzyjmy się prostemu przykładowi użycia tej funkcjonalności.
Polecenie EXPLAIN umożliwia wyświetlenie opisu planu wykonania aktualnego zapytania w formacie „plan advice”. W tym celu należy zastosować atrybut „plan_advice”:explain (plan_advice, costs off) select sum(o_totalprice)
from lineitem, orders, part
where p_partkey = l_partkey
and l_orderkey = o_orderkey
and p_retailprice > 2075;
QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Hash Join
Hash Cond: (lineitem.l_partkey = part.p_partkey)
-> Seq Scan on lineitem
-> Hash
-> Seq Scan on part
Filter: (p_retailprice > '2075'::numeric)
-> Index Scan using orders_pkey on orders
Index Cond: (o_orderkey = lineitem.l_orderkey)
Generated Plan Advice:
JOIN_ORDER(lineitem part orders)
NESTED_LOOP_PLAIN(orders)
HASH_JOIN(part)
SEQ_SCAN(lineitem part)
INDEX_SCAN(orders public.orders_pkey)
NO_GATHER(lineitem orders part)
Wyświetlony opis „Generated Plan Advice” zawiera kluczowe decyzje podjęte przez Query Planner. W przedstawionym przykładzie są to:
– kolejność łączenia tabel – JOIN_ORDER
– typy algorytmów łączenia tabel – NESTED_LOOP_PLAIN, HASH_JOIN
– użycie indeksów – INDEX_SCAN
– użycie operacji pełnego odczytu tabeli – SEQ_SCAN
– nieużywanie Parallel Query – NO_GATHER
O ile sam opis planu wykonania przedstawiony w formacie „plan advice” nie jest szczególnie odkrywczy, gdyż wynika wprost ze szczegółowego zapisu planu wykonania, o tyle ciekawe są możliwości ingerowania w jego treść. Za pomocą nowego parametru sesyjnego pg_plan_advice.advice programista może Query Plannerowi narzucić własną decyzję do wykorzystania podczas generowania planu wykonania zapytania. Przykładowo, zmieńmy algorytm łączenia tabel lineitem i part z Hash Join na Merge Join:
set pg_plan_advice.advice = 'MERGE_JOIN_PLAIN(part)';
explain (plan_advice, costs off) select sum(o_totalprice)
from lineitem, orders, part
where p_partkey = l_partkey
and l_orderkey = o_orderkey
and p_retailprice > 2075;QUERY PLAN
---------------------------------------------------------------------
Aggregate
-> Nested Loop
-> Merge Join
Merge Cond: (lineitem.l_partkey = part.p_partkey)
-> Sort
Sort Key: lineitem.l_partkey
-> Seq Scan on lineitem
-> Sort
Sort Key: part.p_partkey
-> Seq Scan on part
Filter: (p_retailprice > '2075'::numeric)
-> Index Scan using orders_pkey on orders
Index Cond: (o_orderkey = lineitem.l_orderkey)
Supplied Plan Advice:
MERGE_JOIN_PLAIN(part) /* matched */
Generated Plan Advice:
JOIN_ORDER(lineitem part orders)
MERGE_JOIN_PLAIN(part)
NESTED_LOOP_PLAIN(orders)
SEQ_SCAN(lineitem part)
INDEX_SCAN(orders public.orders_pkey)
NO_GATHER(lineitem orders part)
Sekcja „Supplied Plan Advice” wskazuje, jakie decyzje zostały narzucone Query Plannerowi oraz informuje, czy zostały one zaakceptowane (matched).
Instrukcje „plan advice” stanowią formę hintów, znanych z innych systemów czy też z pg_hint_plan. Aktualnie umożliwiają one opisywanie metod dostępu do danych (SQL_SCAN, TID_SCAN, INDEX_SCAN, INDEX_ONLY_SCAN, FOREIGN_JOIN, BITMAP_HEAP_SCAN, DO_NOT_SCAN), kolejności łączenia tabel (JOIN_ORDER), algorytmu łączenia tabel (MERGE_JOIN_MATERIALIZE, MERGE_JOIN_PLAIN, NESTED_LOOP_MATERIALIZE, NESTED_LOOP_MEMOIZE, NESTED_LOOP_PLAIN, HASH_JOIN), łączenia partycji (PARTITIONWISE), unikatowości semijoina (SEMIJOIN_UNIQUE, SEMIJOIN_NON_UNIQUE), zrównoleglenia (GATHER, GATHER_MERGE, NO_GATHER). Brakuje jedynie instrukcji, które pozwoliłyby wpływać na sposób realizacji agregacji.
Dokumentacja: https://www.postgresql.org/docs/19/pgplanadvice.html