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!