Sortowanie przyrostowe w PostgreSQL 13

Jedną z nowych technik optymalizacji wydajności, jakie pojawiły się w PostgreSQL 13 jest sortowanie przyrostowe (incremental sorting). Znajduje ono zastosowanie w zapytaniach, które sortują rekordy według klucza wielokolumnowego. W przeszłości, popularnym sposobem na optymalizację takich sortowań było utworzenie indeksu na wszystkich kolumnach klucza sortowania. Obecnie, serwer PostgreSQL potrafi w takiej sytuacji skorzystać również z indeksu obejmującego tylko część (początkową) kolumn klucza sortowania, a następnie „dosortować” rekordy według końcowych kolumn klucza. Dzięki temu mechanizmowi, PostgreSQL 13 niewątpliwie częściej będzie wykorzystywać indeksy do optymalizacji sortowań, co przełoży się na ogólną poprawę wydajności.

Przedstawmy działanie i efekty wydajnościowe sortowania przyrostowego w PostgreSQL 13. Utworzymy w tym celu trzykolumnową tabelę, zawierającą 10 000 rekordów, a następnie przeanalizujemy plan wykonania zapytania, które sortuje rekordy według dwukolumnowego klucza:

create table demo as 
select random()*1000 as x, random()*1000 as y, random()*1000 as z
from generate_series(1,10000);

explain analyze select z from demo order by x,y limit 1;

                          QUERY PLAN 
-----------------------------------------------------------------
Limit  (cost=214.00..214.00 rows=1 width=24) 
   ->  Sort  (cost=214.00..239.00 rows=10000 width=24) 
         Sort Key: x, y
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on demo  (cost=0.00..164.00 rows=10000) 
Planning Time: 0.131 ms
Execution Time: 3.031 ms

Jak widać, algorytm Quicksort wykonał w pamięci operacyjnej pełne sortowanie rekordów według x,y.

Utwórzmy teraz indeks jedynie na pierwszej kolumnie wykorzystywanej w kluczu sortowania i ponownie przeanalizujmy plan wykonania wcześniejszego zapytania:

create index i_x on demo(x);

explain analyze select z from demo order by x,y limit 1;

                  QUERY PLAN
-----------------------------------------------------------------
Limit  (cost=0.35..0.45 rows=1 width=24)
   ->  Incremental Sort  (cost=0.35..976.27 rows=10000 width=24)
         Sort Key: x, y
         Presorted Key: x
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
         ->  Index Scan using i_x on demo  (cost=0.29..526.27 rows=10000 width=24)
Planning Time: 0.360 ms
Execution Time: 0.077 ms

Zaobserwowaliśmy właśnie użycie sortowania przyrostowego przez PostgreSQL (operacja „Incremental Sort”). Z opisu planu wykonania wynika, że w pierwszej kolejności, indeks i_x posłużył do wstępnego posortowania rekordów według kolumny x, a w drugiej kolejności, w ramach grup rekordów o identycznych wartościach x, nastąpiło „dosortowanie” według kolumny y. PostgreSQL 12 w tej sytuacji nie potrafił zastosować takiego „cząstkowego” indeksu.

Oczywiście, na osiągnięcie jeszcze lepszego rezultatu pozwoliłby indeks utworzony na obu kolumnach klucza sortowania:

create index i_xy on demo(x,y);

explain analyze select z from demo order by x,y limit 1;

    QUERY PLAN                                             
-----------------------------------------------------------------
Limit  (cost=0.29..0.34 rows=1 width=24)
   ->  Index Scan using i_xy on demo  (cost=0.29..570.28 rows=10000 width=24) 
Planning Time: 0.359 ms
Execution Time: 0.060 ms

tyle tylko, że nie zawsze takim indeksem dysponujemy, a skok wydajnościowy nie jest już tak bardzo zauważalny.

Przy okazji wprowadzenia nowej metody sortowania pojawił się też parametr konfiguracyjny, który pozwala ją aktywować/dezaktywować: enable_incremental_sort. Dobrej zabawy!

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *