За всё время моей работы с веб-программистами внезапные и катастрофичные провалы производительности в базах, насколько я помню, всегда имели одну и ту же природу.

Рассмотрим типичную базу данных, которую создали веб-программисты. (Не в нашей компании, конечное, а «какие-то другие програмисты из скверных команий».) Я сейчас утрирую, специально всё свёл к самому минималистичному варианту, на примере которого можно показать эффект провала производительности. «Типичная» база данных веб-программистов состоит из одной таблицы с тремя полями: первичным ключом, полем created_at (датой создания записи) и толстым jsonb, в котором хранятся в ненормализованном виде все данные. Возможны и другие поля, но они сейчас не важны.

create table test (
  pk bigint primary key, 
  c timestamptz not null default CURRENT_TIMESTAMP, 
  j jsonb not null
);
create index on test(c);
create index on test using gin (j);
=> \d test
                              Table "olleg.test"
 Column |           Type           | Collation | Nullable |      Default
--------+--------------------------+-----------+----------+-------------------
 pk     | bigint                   |           | not null |
 c      | timestamp with time zone |           | not null | CURRENT_TIMESTAMP
 j      | jsonb                    |           | not null |
Indexes:
    "test_pkey" PRIMARY KEY, btree (pk)
    "test_c_idx" btree (c)
    "test_j_idx" gin (j)

Типичный запрос к такой БД идёт с фильтрацией по полю jsonb (в моём примере j) и с обратной сортировкой по полю created_at (в моём примере c) с ограничением вывода количества записей — так веб-программисты часто реализуют paging по количеству записей, показывая пользователю наиболее свежие записи. Для моего примера это будет:

select pk from test where j@>'100' order by c desc limit 10;

Если БД небольшая, то всё ок. Числа подобрал условные, лишь бы показать эффект. Примеры показываю на PostgreSQL 10.

truncate test;
insert into test (pk,c,j)
  select pk,clock_timestamp(),array_to_json(array_fill(pk,ARRAY[10]))
    from generate_series(1,100000) as gs(pk);
analyze;
                         List of relations
 Schema | Name | Type  | Owner | Persistence | Size  | Description
--------+------+-------+-------+-------------+-------+-------------
 olleg  | test | table | olleg | permanent   | 20 MB |
(1 row)
 
                                 List of relations
 Schema |    Name    | Type  | Owner | Table | Persistence |  Size   | Description
--------+------------+-------+-------+-------+-------------+---------+-------------
 olleg  | test_c_idx | index | olleg | test  | permanent   | 2208 kB |
 olleg  | test_j_idx | index | olleg | test  | permanent   | 1984 kB |
 olleg  | test_pkey  | index | olleg | test  | permanent   | 2208 kB |
(3 rows)

И в этом случае тестовый запрос выполняется нормально, быстро, и при сканировании используется gin индекс.

=> explain analyze select pk from test where j@>'100' order by c desc limit 10;
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=351.19..351.21 rows=10 width=16) (actual time=5.525..5.526 rows=1 loops=1)
   ->  Sort  (cost=351.19..351.44 rows=100 width=16) (actual time=5.525..5.526 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=248.78..349.02 rows=100 width=16) (actual time=5.519..5.519 rows=1 loops=1)
               Recheck Cond: (j @> '100'::jsonb)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on test_j_idx  (cost=0.00..248.75 rows=100 width=0) (actual time=5.511..5.512 rows=1 loops=1)
                     Index Cond: (j @> '100'::jsonb)
 Planning time: 0.150 ms
 Execution time: 5.546 ms
(11 rows)

Если база подрастёт в десять раз:

truncate test;
insert into test (pk,c,j)
  select pk,clock_timestamp(),array_to_json(array_fill(pk,ARRAY[10]))
  from generate_series(1,1000000) as gs(pk);
analyze;
                         List of relations
 Schema | Name | Type  | Owner | Persistence |  Size  | Description
--------+------+-------+-------+-------------+--------+-------------
 olleg  | test | table | olleg | permanent   | 205 MB |
(1 row)
 
                                List of relations
 Schema |    Name    | Type  | Owner | Table | Persistence | Size  | Description
--------+------------+-------+-------+-------+-------------+-------+-------------
 olleg  | test_c_idx | index | olleg | test  | permanent   | 21 MB |
 olleg  | test_j_idx | index | olleg | test  | permanent   | 46 MB |
 olleg  | test_pkey  | index | olleg | test  | permanent   | 21 MB |
(3 rows)

…то тогда планировщик начинает отказываться от использования gin в этом запросе.

explain analyze select pk from test where j@>'100' order by c desc limit 10;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..465.54 rows=10 width=16) (actual time=402.973..403.016 rows=1 loops=1)
   ->  Index Scan Backward using test_c_idx on test  (cost=0.42..46511.43 rows=1000 width=16) (actual time=402.972..403.015 rows=1 loops=1)
         Filter: (j @> '100'::jsonb)
         Rows Removed by Filter: 999999
 Planning time: 0.132 ms
 Execution time: 403.026 ms
(6 rows)

Причём совершенно напрасно, если искусственно запретить использовать индекс на created_at для сортировки, например если удалить этот индекс (так и было сделано во время аварийной ситуации), то скорость такого запроса вырастет в этой БД в 34 раза. То есть явно это ошибка планировщика — отказываться от использования gin индекса.

drop index test_c_idx;
explain analyze select pk from test where j@>'100' order by c desc limit 10;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1423.86..1423.88 rows=10 width=16) (actual time=11.644..11.646 rows=1 loops=1)
   ->  Sort  (cost=1423.86..1426.36 rows=1000 width=16) (actual time=11.643..11.644 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=407.75..1402.25 rows=1000 width=16) (actual time=11.633..11.634 rows=1 loops=1)
               Recheck Cond: (j @> '100'::jsonb)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on test_j_idx  (cost=0.00..407.50 rows=1000 width=0) (actual time=11.613..11.614 rows=1 loops=1)
                     Index Cond: (j @> '100'::jsonb)
 Planning time: 0.240 ms
 Execution time: 11.694 ms
(11 rows)

Другой способ обойти этот баг, который обычно и применяется, это создавать btree индексы непосредственно по полям, по которым проходит фильтрация. Чтобы это показать, я видоизменил поисковый запрос так, чтобы поиск вёлся не по всему массиву, а по конкретному элементу.

=> explain analyze select pk from test where j->>1='100' order by c desc limit 10;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..98.45 rows=10 width=16) (actual time=289.033..289.061 rows=1 loops=1)
   ->  Index Scan Backward using test_c_idx on test  (cost=0.42..49011.43 rows=5000 width=16) (actual time=289.032..289.059 rows=1 loops=1)
         Filter: ((j ->> 1) = '100'::text)
         Rows Removed by Filter: 999999
 Planning time: 0.065 ms
 Execution time: 289.078 ms
(6 rows)
 
create index on test((j->>1));
analyze;
 
=> explain analyze select pk from test where j->>1='100' order by c desc limit 10;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2.45..2.46 rows=1 width=16) (actual time=0.066..0.067 rows=1 loops=1)
   ->  Sort  (cost=2.45..2.46 rows=1 width=16) (actual time=0.066..0.066 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Index Scan using test_expr_idx on test  (cost=0.42..2.44 rows=1 width=16) (actual time=0.047..0.048 rows=1 loops=1)
               Index Cond: ((j ->> 1) = '100'::text)
 Planning time: 0.273 ms
 Execution time: 0.086 ms
(8 rows)

Тут выигрыш в 3,3 тысячи раз по сравнению со случаем, когда индексы используются не для фильтрации, а для сортировки.

Всё это приводило к внезапным и катастрофичным падениям производительности БД. Один вариант такого отказа — это создание полезного индекса по полю created_at, от чего ожидался только прирост производительности, но внезапно происходила практически остановка сервиса. Впрочем, тут хотя бы легко было догадаться, на что грешить и как вернуть работоспособность.

Гораздо более коварен другой вариант, когда индекс по полю created_at уже существует. Пока БД относительно небольшого размера, всё замечательно работает. Нет никаких правок, миграций, эксцессов, ничто не предвещает беды. Но база потихонечку растёт, в один прекрасный момент внезапно переваливает какой-то пороговый размер и планировщик начинает отказываться использовать gin для фильтрации, предпочитая перескочить на btree для обратной сортировки по датам. И это настолько сильно увеличивало время выполнения многих запросов, что сервис можно было считать полностью неработоспособным. И никаких очевидных причин катастрофы не наблюдалось.

Для того чтобы протестировать зависимость этого бага от версии PostgreSQL, я использовал следующий скрипт:

begin;
create table test (
  pk bigint primary key, 
  c timestamptz not null default CURRENT_TIMESTAMP, 
  j jsonb not null);
create index on test(c);
create index on test using gin (j);
insert into test (pk,c,j) 
  select pk,clock_timestamp(),array_to_json(array_fill(pk,ARRAY[10])) 
    from generate_series(1,100000) as gs(pk);
analyze;
explain analyze select pk from test where j@>'100' order by c desc limit 10;
drop table test;
create table test (pk bigint primary key, 
                   c timestamptz not null default CURRENT_TIMESTAMP, 
                   j jsonb not null);
create index on test(c);
create index on test using gin (j);
insert into test (pk,c,j) 
  select pk,clock_timestamp(),array_to_json(array_fill(pk,ARRAY[10])) 
    from generate_series(1,1000000) as gs(pk);
analyze;
explain analyze select pk from test where j@>'100' order by c desc limit 10;
rollback;

Проблема наблюдалась на версиях: 9.5, 9.6, 10, 11 и 12.

$ psql -p 5412 -f test.sql
BEGIN
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 100000
ANALYZE
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=351.19..351.21 rows=10 width=16) (actual time=4.697..4.698 rows=1 loops=1)
   ->  Sort  (cost=351.19..351.44 rows=100 width=16) (actual time=4.697..4.697 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=248.78..349.02 rows=100 width=16) (actual time=4.691..4.691 rows=1 loops=1)
               Recheck Cond: (j @> '100'::jsonb)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on test_j_idx  (cost=0.00..248.75 rows=100 width=0) (actual time=4.687..4.687 rows=1 loops=1)
                     Index Cond: (j @> '100'::jsonb)
 Planning Time: 0.188 ms
 Execution Time: 4.731 ms
(11 rows)
 
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 1000000
ANALYZE
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..465.54 rows=10 width=16) (actual time=425.537..425.580 rows=1 loops=1)
   ->  Index Scan Backward using test_c_idx on test  (cost=0.42..46511.43 rows=1000 width=16) (actual time=425.536..425.579 rows=1 loops=1)
         Filter: (j @> '100'::jsonb)
         Rows Removed by Filter: 999999
 Planning Time: 0.174 ms
 Execution Time: 425.590 ms
(6 rows)
 
ROLLBACK

Тестовый скрипт не показал проблему на: 13 и 14.

$ psql -p 5413 -f test.sql
BEGIN
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 100000
ANALYZE
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=258.37..258.39 rows=10 width=16) (actual time=5.833..5.834 rows=1 loops=1)
   ->  Sort  (cost=258.37..258.39 rows=10 width=16) (actual time=5.831..5.831 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=248.08..258.20 rows=10 width=16) (actual time=5.819..5.820 rows=1 loops=1)
               Recheck Cond: (j @> '100'::jsonb)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on test_j_idx  (cost=0.00..248.07 rows=10 width=0) (actual time=5.813..5.814 rows=1 loops=1)
                     Index Cond: (j @> '100'::jsonb)
 Planning Time: 0.280 ms
 Execution Time: 5.866 ms
(11 rows)
 
DROP TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 1000000
ANALYZE
                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=504.19..504.21 rows=10 width=16) (actual time=8.436..8.437 rows=1 loops=1)
   ->  Sort  (cost=504.19..504.44 rows=100 width=16) (actual time=8.435..8.436 rows=1 loops=1)
         Sort Key: c DESC
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on test  (cost=400.77..502.02 rows=100 width=16) (actual time=8.429..8.430 rows=1 loops=1)
               Recheck Cond: (j @> '100'::jsonb)
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on test_j_idx  (cost=0.00..400.75 rows=100 width=0) (actual time=8.412..8.412 rows=1 loops=1)
                     Index Cond: (j @> '100'::jsonb)
 Planning Time: 0.215 ms
 Execution Time: 8.453 ms
(11 rows)
 
ROLLBACK

Я поискал в changelog к PostgreSQL 13, но ничего конкретного не нашёл. Хотя в этой версии были какие-то общие оптимизации планировщика, подходящие по смыслу, например:

Improve the optimizer's selectivity estimation for containment/match operators (Tom Lane)

Вообще, можно взять за правило ограничение в использовании jsonb. Его удобно использовать для тех случаев, когда его данные не используются для работы БД, его можно легко и просто сразу передать веб-страницам. Если же по данным осуществляются операции БД, например, фильтрация, сортировка, группировка и объединения, то такие данные лучше хранить вне jsonb, в структуре самой БД. Это связано с тем, что Postgres не умеет собирать статистику по внутренностям jsonb, он рассматривает его только целиком, что обычно совершенно бессмысленно (можно отключать, так как статистика по jsonb сильно раздувает таблицу статистики). Поэтому для запросов по своим собственным структурам он строит план гораздо качественнее, чем по данным внутри jsonb.