Возможно ли ускорить выборку?

Question

[Андрей]

Нужно вытаскивать из массива jsonb объекты со смещением, есть два варианта:

select
  jsonb_array_elements(content->'blocks') as content
from
  data
where
  "order" = 2 and external_id = 1
limit 100 offset 95500;

select
  block
from (
    select
      jsonb_array_elements(content->'blocks') as block
    from
      data
) subquery
    where
      (block->>'id')::int between 95500 and 95600;

Второй на 100 000 элементов отрабатывает значительно быстрее, но нет возможности сделать id числовыми, сейчас это строки. Возможно как-то ускорить выборку для первого варианта?

PosgtreSQL 17

Comments

[Сергей Горностаев]

Надо план запроса смотреть, чтобы понять возможность.

[Андрей]

Вариант с offset


Вариант с between


Про "значительно быстрее" все же преувеличил, разница у них небольшая, но во втором варианте, как я понимаю, можно индекс сделать по числовому id, а вот как это решить со строковым id

[Everything_is_bad]

по json полям тоже можно сделать индекс, но тут проблема в другом, если возникает нужда поиска внутри json, то стоит выносить это в поля и делать нормальную структура таблиц

Answer 1

[Сергей Соловьев]

Не знаю бизнес-логику, но первый и второй запросы должны дать разные результаты - не факт, что у тебя ID будет указывать на точное расположение записи в этой таблице, т.е. LIMIT 100 OFFSET 95500 и WHERE id BETWEEN 95500 AND 95600 - это разные вещи.

И еще - большая часть ресурсов уходит на то, чтобы вызвать jsonb_array_contents. ProjectSet узел дает больший вес. Скорее всего, это потому что там находится 95600 элементов в массиве.

Скорее всего, ты хочешь реализовать пагинацию. В PostgreSQL для этого существуют курсоры:

DECLARE array_elements CURSOR FOR
select
  jsonb_array_elements(content->'blocks') as content
from
  data
where
  "order" = 2 and external_id = 1;

А потом используешь его:

FETCH 100 FROM array_elements;

Но проблему это скорее всего не решит. Здесь лучше хранить эти элементы массива в другой таблице. Например, такой:

CREATE TABLE array_elements(
   order integer REFERENCES data("order"),
   external_id integer REFERENCES data(external_id),
   element jsonb
);
CREATE INDEX array_elements_order_external_id_idx on array_elements(order, external_id);

Использовать курсор при этом не запрещается.