Jedn\u0105 z nowych technik optymalizacji wydajno\u015bci, jakie pojawi\u0142y si\u0119 w PostgreSQL 13 jest sortowanie przyrostowe (incremental sorting). Znajduje ono zastosowanie w zapytaniach, kt\u00f3re sortuj\u0105 rekordy wed\u0142ug klucza wielokolumnowego. W przesz\u0142o\u015bci, popularnym sposobem na optymalizacj\u0119 takich sortowa\u0144 by\u0142o utworzenie indeksu na wszystkich kolumnach klucza sortowania. Obecnie, serwer PostgreSQL potrafi w takiej sytuacji skorzysta\u0107 r\u00f3wnie\u017c z indeksu obejmuj\u0105cego tylko cz\u0119\u015b\u0107 (pocz\u0105tkow\u0105) kolumn klucza sortowania, a nast\u0119pnie „dosortowa\u0107” rekordy wed\u0142ug ko\u0144cowych kolumn klucza. Dzi\u0119ki temu mechanizmowi, PostgreSQL 13 niew\u0105tpliwie cz\u0119\u015bciej b\u0119dzie wykorzystywa\u0107 indeksy do optymalizacji sortowa\u0144, co prze\u0142o\u017cy si\u0119 na og\u00f3ln\u0105 popraw\u0119 wydajno\u015bci.<\/p>\n\n\n\n
Przedstawmy dzia\u0142anie i efekty wydajno\u015bciowe sortowania przyrostowego w PostgreSQL 13. Utworzymy w tym celu trzykolumnow\u0105 tabel\u0119, zawieraj\u0105c\u0105 10 000 rekord\u00f3w, a nast\u0119pnie przeanalizujemy plan wykonania zapytania, kt\u00f3re sortuje rekordy wed\u0142ug dwukolumnowego klucza:<\/p>\n\n\n\n
create table demo as \nselect random()*1000 as x, random()*1000 as y, random()*1000 as z\nfrom generate_series(1,10000);\n\nexplain analyze select z from demo order by x,y limit 1;\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 QUERY PLAN\u00a0\n-----------------------------------------------------------------\nLimit\u00a0 (cost=214.00..214.00 rows=1 width=24) \n\u00a0\u00a0 ->\u00a0 Sort\u00a0 (cost=214.00..239.00 rows=10000 width=24) \n\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Sort Key: x, y\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sort Method: top-N heapsort\u00a0 Memory: 25kB\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ->\u00a0 Seq Scan on demo\u00a0 (cost=0.00..164.00 rows=10000) \nPlanning Time: 0.131 ms\nExecution Time: 3.031 ms<\/pre>\n\n\n\nJak wida\u0107, algorytm Quicksort wykona\u0142 w pami\u0119ci operacyjnej pe\u0142ne sortowanie rekord\u00f3w wed\u0142ug x,y.<\/p>\n\n\n\n
Utw\u00f3rzmy teraz indeks jedynie na pierwszej kolumnie wykorzystywanej w kluczu sortowania i ponownie przeanalizujmy plan wykonania wcze\u015bniejszego zapytania:<\/p>\n\n\n\n
create index i_x on demo(x);\n\nexplain analyze select z from demo order by x,y limit 1;\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 QUERY PLAN\n-----------------------------------------------------------------\nLimit\u00a0 (cost=0.35..0.45 rows=1 width=24)\n\u00a0\u00a0 ->\u00a0 Incremental Sort\u00a0 (cost=0.35..976.27 rows=10000 width=24)\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sort Key: x, y\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Presorted Key: x\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Full-sort Groups: 1\u00a0 Sort Method: quicksort\u00a0 Average Memory: 25kB\u00a0 Peak Memory: 25kB\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ->\u00a0 Index Scan using i_x on demo\u00a0 (cost=0.29..526.27 rows=10000 width=24)\nPlanning Time: 0.360 ms\nExecution Time: 0.077 ms<\/pre>\n\n\n\nZaobserwowali\u015bmy w\u0142a\u015bnie u\u017cycie sortowania przyrostowego przez PostgreSQL (operacja „Incremental Sort”). Z opisu planu wykonania wynika, \u017ce w pierwszej kolejno\u015bci, indeks
i_x<\/code> pos\u0142u\u017cy\u0142 do wst\u0119pnego posortowania rekord\u00f3w wed\u0142ug kolumny x, a w drugiej kolejno\u015bci, w ramach grup rekord\u00f3w o identycznych warto\u015bciach x, nast\u0105pi\u0142o „dosortowanie” wed\u0142ug kolumny y. PostgreSQL 12 w tej sytuacji nie potrafi\u0142 zastosowa\u0107 takiego „cz\u0105stkowego” indeksu.<\/p>\n\n\n\nOczywi\u015bcie, na osi\u0105gni\u0119cie jeszcze lepszego rezultatu pozwoli\u0142by indeks utworzony na obu kolumnach klucza sortowania:<\/p>\n\n\n\n
create index i_xy on demo(x,y);\n\nexplain analyze select z from demo order by x,y limit 1;\n\n QUERY PLAN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\n-----------------------------------------------------------------\nLimit\u00a0 (cost=0.29..0.34 rows=1 width=24)\n\u00a0\u00a0 ->\u00a0 Index Scan using i_xy on demo\u00a0 (cost=0.29..570.28 rows=10000 width=24) \nPlanning Time: 0.359 ms\nExecution Time: 0.060 ms<\/pre>\n\n\n\ntyle tylko, \u017ce nie zawsze takim indeksem dysponujemy, a skok wydajno\u015bciowy nie jest ju\u017c tak bardzo zauwa\u017calny.<\/p>\n\n\n\n