Wraz z wprowadzeniem wersji 14 PostgreSQL pojawiła się możliwość – na którą długo czekaliśmy – definiowania statystyk rozszerzonych dla wyrażeń. Jest to mechanizm znany np. z Oracle Database, wspomagający szacowanie selektywności predykatów opartych o funkcje/wyrażenia. Wiemy, jak ważne jest trafne szacowanie selektywności predykatów występujących w zapytaniach – od tego przecież zależy precyzja oszacowania kosztów planów wykonania zapytania i tym samym wybór planu optymalnego. W domyślnej konfiguracji, PostgreSQL gromadzi statystyki dla każdej kolumny każdej tabeli (statystyki jednokolumnowe) i na ich podstawie podejmuje się przewidywania liczby (procentu) rekordów, które spełnią podane przez programistę warunki selekcji. Takie rozwiązania dobrze sprawdza się w sytuacji, gdy warunki selekcji są bezpośrednio oparte o jedną kolumnę tabeli lub o kilka kolumn nieskorelowanych ze sobą. Problemy pojawiają się wtedy, kiedy pomiędzy kolumnami tabeli występują zależności funkcyjne lub kiedy warunek selekcji opiera się na wyrażeniu wyliczanym na podstawie źródłowej kolumny. We wcześniejszych wersjach PostgreSQL, z problemem kolumn skorelowanych radziliśmy sobie definiując wielokolumnowe statystyki rozszerzone (create statistics), natomiast na potrzeby trafnego szacowania selektywności wyrażeń zawsze konieczne było tworzenie indeksów funkcyjnych. Nawet, jeżeli nie miały one służyć poprawie wydajności zapytania, stanowiły niezbędne źródło statystyk opisujących rozkład wartości wyrażenia. Gdy ich brakowało, Query Planner przyjmował, że niezależnie od rodzaju użytego wyrażenia, jego selektywność w predykatach równościowych wynosiła zawsze 0.5%.
Za pomocą prostego przykładu zilustrujemy skuteczność statystyk rozszerzonych dla wyrażeń. Dane jest zapytanie, które łączy dwie tabele oraz dokonuje selekcji rekordów – właśnie w oparciu o wyrażenie. Tabela CARS posiada 100 tysięcy rekordów, w tym około 53 tysiące audi, tabela RESERVATIONS posiada cztery rekordy. Analiza planu wykonania tego zapytania pokazuje nietrafność oszacowania selektywności wyrażenia UPPER(MAKE)=’AUDI’. Query Planner „spodziewał się” zaledwie 500 rekordów (czyli po prostu 0.5% z 100.000), podczas gdy w rzeczywistym wykonaniu było ich 53.656. W tej sytuacji, w lepszym szacowaniu pomógłby indeks na UPPER(MAKE), ale niestety właśnie z powodu wysokoprocentowej selektywności jego utworzenie było niestety bezzasadne. Zauważmy jednocześnie, że prawdopodobnie z powodu znacznego niedoszacowania liczby rekordów, które spełnią warunek UPPER(MAKE)=’AUDI’, Query Planner postanowił zrealizować połączenie tabel za pomocą algorytmu Nested Loops. Tak „zoptymalizowane” zapytanie wykonało się w czasie 428 ms.
explain analyze select * from reservations r join cars c on (r.c_id=c.c_id) where upper(make)='AUDI';QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..5378.05 rows=1 width=285) (actual time=15.830..428.009 rows=2 loops=1)
Join Filter: (r.c_id = c.c_id)
Rows Removed by Join Filter: 214622
-> Seq Scan on cars c (cost=0.00..5347.00 rows=500 width=273) (actual time=0.031..235.348 rows=53656 loops=1)
Filter: (upper((make)::text) = 'AUDI'::text)
Rows Removed by Filter: 46344
-> Materialize (cost=0.00..1.06 rows=4 width=12) (actual time=0.000..0.001 rows=4 loops=53656)
-> Seq Scan on reservations r (cost=0.00..1.04 rows=4 width=12) (actual time=0.008..0.010 rows=4 loops=1)
Planning Time: 0.186 ms
Execution Time: 428.055 ms
Teraz skorzystajmy ze wspomnianej nowej możliwości, jaką daje PostgreSQL 14 – zdefiniujmy statystyki rozszerzone dla wyrażenia UPPER(MAKE):
create statistics cars_make_model_stts on upper(make) from cars;
analyze cars;Ponowna analiza planu wykonania zapytania pokazuje trafne, tym razem, oszacowanie liczby rekordów, spełniających warunek UPPER(MAKE)=’AUDI’ – na poziomie 53603 wobec 53656 faktycznych (drobny błąd próbkowania statystyk). Zarazem Query Planner postanowił wybrać algorytm Hash Join do realizacji połączenia table – słuszny w obliczu względnie dużej liczby rekordów pochodzących z tabeli CARS. W rezultacie, czas wykonania zapytania skrócił się do 279 ms, czyli o 1/3 szybciej.
explain analyze select * from reservations r join cars c on (r.c_id=c.c_id) where upper(make)='AUDI';
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Hash Join (cost=1.09..5549.12 rows=2 width=285) (actual time=8.573..279.335 rows=2 loops=1)
Hash Cond: (c.c_id = r.c_id)
-> Seq Scan on cars c (cost=0.00..5347.00 rows=53603 width=273) (actual time=0.029..254.158 rows=53656 loops=1)
Filter: (upper((make)::text) = 'AUDI'::text)
Rows Removed by Filter: 46344
-> Hash (cost=1.04..1.04 rows=4 width=12) (actual time=0.014..0.016 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on reservations r (cost=0.00..1.04 rows=4 width=12) (actual time=0.007..0.009 rows=4 loops=1)
Planning Time: 0.379 ms
Execution Time: 279.382 ms
Wnioski końcowe wydają się być oczywiste: warto rozważać definiowanie statystyk rozszerzonych dla wyrażeń, które często powtarzają się w warunkach selekcji wykonywanych zapytań, a na których nie zbudowano indeksów funkcyjnych. Zdefiniowanie statystyki rozszerzonej jest czynnością jednorazową. W przyszłości statystyka taka będzie zbierana automatycznie podczas każdej operacji ANALYZE.