Wraz z wprowadzeniem wersji 14 PostgreSQL pojawi\u0142a si\u0119 mo\u017cliwo\u015b\u0107 – na kt\u00f3r\u0105 d\u0142ugo czekali\u015bmy – definiowania statystyk rozszerzonych dla wyra\u017ce\u0144. Jest to mechanizm znany np. z Oracle Database, wspomagaj\u0105cy szacowanie selektywno\u015bci predykat\u00f3w opartych o funkcje\/wyra\u017cenia. Wiemy, jak wa\u017cne jest trafne szacowanie selektywno\u015bci predykat\u00f3w wyst\u0119puj\u0105cych w zapytaniach – od tego przecie\u017c zale\u017cy precyzja oszacowania koszt\u00f3w plan\u00f3w wykonania zapytania i tym samym wyb\u00f3r planu optymalnego. W domy\u015blnej konfiguracji, PostgreSQL gromadzi statystyki dla ka\u017cdej kolumny ka\u017cdej tabeli (statystyki jednokolumnowe) i na ich podstawie podejmuje si\u0119 przewidywania liczby (procentu) rekord\u00f3w, kt\u00f3re spe\u0142ni\u0105 podane przez programist\u0119 warunki selekcji. Takie rozwi\u0105zania dobrze sprawdza si\u0119 w sytuacji, gdy warunki selekcji s\u0105 bezpo\u015brednio oparte o jedn\u0105 kolumn\u0119 tabeli lub o kilka kolumn nieskorelowanych ze sob\u0105. Problemy pojawiaj\u0105 si\u0119 wtedy, kiedy pomi\u0119dzy kolumnami tabeli wyst\u0119puj\u0105 zale\u017cno\u015bci funkcyjne lub kiedy warunek selekcji opiera si\u0119 na wyra\u017ceniu wyliczanym na podstawie \u017ar\u00f3d\u0142owej kolumny. We wcze\u015bniejszych wersjach PostgreSQL, z problemem kolumn skorelowanych radzili\u015bmy sobie definiuj\u0105c wielokolumnowe statystyki rozszerzone (create statistics), natomiast na potrzeby trafnego szacowania selektywno\u015bci wyra\u017ce\u0144 zawsze konieczne by\u0142o tworzenie indeks\u00f3w funkcyjnych. Nawet, je\u017celi nie mia\u0142y one s\u0142u\u017cy\u0107 poprawie wydajno\u015bci zapytania, stanowi\u0142y niezb\u0119dne \u017ar\u00f3d\u0142o statystyk opisuj\u0105cych rozk\u0142ad warto\u015bci wyra\u017cenia. Gdy ich brakowa\u0142o, Query Planner przyjmowa\u0142, \u017ce niezale\u017cnie od rodzaju u\u017cytego wyra\u017cenia, jego selektywno\u015b\u0107 w predykatach r\u00f3wno\u015bciowych wynosi\u0142a zawsze 0.5%.<\/p>\n\n\n\n
Za pomoc\u0105 prostego przyk\u0142adu zilustrujemy skuteczno\u015b\u0107 statystyk rozszerzonych dla wyra\u017ce\u0144. Dane jest zapytanie, kt\u00f3re \u0142\u0105czy dwie tabele oraz dokonuje selekcji rekord\u00f3w – w\u0142a\u015bnie w oparciu o wyra\u017cenie. Tabela CARS posiada 100 tysi\u0119cy rekord\u00f3w, w tym oko\u0142o 53 tysi\u0105ce audi, tabela RESERVATIONS posiada cztery rekordy. Analiza planu wykonania tego zapytania pokazuje nietrafno\u015b\u0107 oszacowania selektywno\u015bci wyra\u017cenia UPPER(MAKE)=’AUDI’. Query Planner „spodziewa\u0142 si\u0119” zaledwie 500 rekord\u00f3w (czyli po prostu 0.5% z 100.000), podczas gdy w rzeczywistym wykonaniu by\u0142o ich 53.656. W tej sytuacji, w lepszym szacowaniu pom\u00f3g\u0142by indeks na UPPER(MAKE), ale niestety w\u0142a\u015bnie z powodu wysokoprocentowej selektywno\u015bci jego utworzenie by\u0142o niestety bezzasadne. Zauwa\u017cmy jednocze\u015bnie, \u017ce prawdopodobnie z powodu znacznego niedoszacowania liczby rekord\u00f3w, kt\u00f3re spe\u0142ni\u0105 warunek UPPER(MAKE)=’AUDI’, Query Planner postanowi\u0142 zrealizowa\u0107 po\u0142\u0105czenie tabel za pomoc\u0105 algorytmu Nested Loops. Tak „zoptymalizowane” zapytanie wykona\u0142o si\u0119 w czasie 428 ms.<\/p>\n\n\n\n
explain analyze select * from reservations r join cars c on (r.c_id=c.c_id) where upper(make)='AUDI';<\/code><\/pre>\n\n\n\nQUERY PLAN \n------------------------------------------------------------------------------------------------------------------\n Nested Loop (cost=0.00..5378.05 rows=1 width=285) (actual time=15.830..428.009 rows=2 loops=1)\n Join Filter: (r.c_id = c.c_id)\n Rows Removed by Join Filter: 214622\n -> Seq Scan on cars c (cost=0.00..5347.00 rows=500 width=273) (actual time=0.031..235.348 rows=53656 loops=1)\n Filter: (upper((make)::text) = 'AUDI'::text)\n Rows Removed by Filter: 46344\n -> Materialize (cost=0.00..1.06 rows=4 width=12) (actual time=0.000..0.001 rows=4 loops=53656)\n -> Seq Scan on reservations r (cost=0.00..1.04 rows=4 width=12) (actual time=0.008..0.010 rows=4 loops=1)\n Planning Time: 0.186 ms\n Execution Time: 428.055 ms\n<\/code><\/pre>\n\n\nTeraz skorzystajmy ze wspomnianej nowej mo\u017cliwo\u015bci, jak\u0105 daje PostgreSQL 14 – zdefiniujmy statystyki rozszerzone dla wyra\u017cenia UPPER(MAKE):<\/p>\n\n\n
create statistics cars_make_model_stts on upper(make) from cars; \nanalyze cars;<\/code><\/pre>\n\n\nPonowna analiza planu wykonania zapytania pokazuje trafne, tym razem, oszacowanie liczby rekord\u00f3w, spe\u0142niaj\u0105cych warunek UPPER(MAKE)=’AUDI’ – na poziomie 53603 wobec 53656 faktycznych (drobny b\u0142\u0105d pr\u00f3bkowania statystyk). Zarazem Query Planner postanowi\u0142 wybra\u0107 algorytm Hash Join do realizacji po\u0142\u0105czenia table – s\u0142uszny w obliczu wzgl\u0119dnie du\u017cej liczby rekord\u00f3w pochodz\u0105cych z tabeli CARS. W rezultacie, czas wykonania zapytania skr\u00f3ci\u0142 si\u0119 do 279 ms, czyli o 1\/3 szybciej.<\/p>\n\n\n
explain analyze select * from reservations r join cars c on (r.c_id=c.c_id) where upper(make)='AUDI';\nQUERY PLAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n---------------------------------------------------------------------------------------------------------------------\n\u00a0Hash Join\u00a0 (cost=1.09..5549.12 rows=2 width=285) (actual time=8.573..279.335 rows=2 loops=1)\n\u00a0\u00a0Hash Cond: (c.c_id = r.c_id)\n\u00a0\u00a0->\u00a0 Seq Scan on cars c\u00a0 (cost=0.00..5347.00 rows=53603 width=273) (actual time=0.029..254.158 rows=53656 loops=1)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Filter: (upper((make)::text) = 'AUDI'::text)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Rows Removed by Filter: 46344\n\u00a0\u00a0\u00a0->\u00a0 Hash\u00a0 (cost=1.04..1.04 rows=4 width=12) (actual time=0.014..0.016 rows=4 loops=1)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Buckets: 1024\u00a0 Batches: 1\u00a0 Memory Usage: 9kB\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0->\u00a0 Seq Scan on reservations r\u00a0 (cost=0.00..1.04 rows=4 width=12) (actual time=0.007..0.009 rows=4 loops=1)\n\u00a0Planning Time: 0.379 ms\n\u00a0Execution Time: 279.382 ms\n<\/code><\/pre>\n\n\nWnioski ko\u0144cowe wydaj\u0105 si\u0119 by\u0107 oczywiste: warto rozwa\u017ca\u0107 definiowanie statystyk rozszerzonych dla wyra\u017ce\u0144, kt\u00f3re cz\u0119sto powtarzaj\u0105 si\u0119 w warunkach selekcji wykonywanych zapyta\u0144, a na kt\u00f3rych nie zbudowano indeks\u00f3w funkcyjnych. Zdefiniowanie statystyki rozszerzonej jest czynno\u015bci\u0105 jednorazow\u0105. W przysz\u0142o\u015bci statystyka taka b\u0119dzie zbierana automatycznie podczas ka\u017cdej operacji ANALYZE.<\/p>\n
<\/p>","protected":false},"excerpt":{"rendered":"