Как оптимизировать sql запрос?

Question

[xiiicool]

В запросе используется оконная функция которая достаточно сильно тормозит запрос, на все поля в partition и order сделаны индексы, сервер 16 ядер 70 гиг оперативки

select rr.*
from (select rr.*,
             case
                 when rr.startAt > max(rr.endAt)
                                       over (partition by rr.value,
                                           rr.rateTimemaskUUID, rr.billIncUUID, rr.notes,
                                           rr.name,
                                           rr.destinationUUID
                                           order by rr.startAt, rr.endAt, rr.dialCode
                                           rows between unbounded preceding and 1 preceding)
                     or max(rr.endAt)
                            over (partition by rr.value,
                                rr.rateTimemaskUUID, rr.billIncUUID, rr.notes,
                                rr.name, rr.destinationUUID
                                order by rr.startAt, rr.endAt, rr.dialCode
                                rows between unbounded preceding and 1 preceding) is null then 1
                 else 0
                 end as is_new_rate
      from rates rr
               left join products p on (p.uuid = rr.productUUID)
               left join destinations d on (d.uuid = rr.destinationUUID)
      WHERE p.carrierUUID = 'd255b155-b8b3-479c-847d-d93ef4545f8a'
        AND (
          p.carrierUUID in
          (select c.uuid
           from carriers c
           where c.countryUUID = '25e5740d-f556-40fb-bc51-7a99b35630b3')
          )
        AND p.direction = '1') rr

explain выглядит так

[
  {
    "id": 1,
    "select_type": "PRIMARY",
    "table": "<derived2>",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 1984,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "c",
    "partitions": null,
    "type": "const",
    "possible_keys": "PRIMARY,carriers_countryUUID_foreign_idx",
    "key": "PRIMARY",
    "key_len": "144",
    "ref": "const",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using temporary; Using filesort"
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "p",
    "partitions": null,
    "type": "ref",
    "possible_keys": "PRIMARY,carrierUUID",
    "key": "carrierUUID",
    "key_len": "145",
    "ref": "const",
    "rows": 15,
    "filtered": 10,
    "Extra": "Using index condition; Using where"
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "rr",
    "partitions": null,
    "type": "ref",
    "possible_keys": "rates_productUUID_foreign_idx",
    "key": "rates_productUUID_foreign_idx",
    "key_len": "145",
    "ref": "billing_api.p.uuid",
    "rows": 1322,
    "filtered": 100,
    "Extra": null
  },
  {
    "id": 2,
    "select_type": "DERIVED",
    "table": "d",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY",
    "key": "PRIMARY",
    "key_len": "144",
    "ref": "billing_api.rr.destinationUUID",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using index"
  }
]

Comments

[Ипатьев]

А можно попросить добавить в ответ результат EXPLAIN FORMAT=JSON?
Не то чтобы я прямо обещаю по нему сразу дать ответ, но во всяком случае читать его попроще, ну и поинформативнее.

[xiiicool]

Ипатьев, Добавил

[Akina]

1. Переписать на CTE
2. Переписать CASE на обратный и избавиться от двойного вычисления
3. Заменить WHERE IN на WHERE EXISTS или INNER JOIN

[Rsa97]

4. Заменить одиночные индексы по каждому полю на составные в нужном порядке.

[Дмитрий]

Простите великодушно, а в чем смысл этой конструкции?

p.carrierUUID = 'd255b155-b8b3-479c-847d-d93ef4545f8a'
        AND (
          p.carrierUUID in
          (select c.uuid
           from carriers c
           where c.countryUUID = '25e5740d-f556-40fb-bc51-7a99b35630b3')
          )

у вас сначала p.carrierUUID сравнивается с const а потом с id из вложенного селекта? Это так проверяется что carrier принадлежит стране с ID 25e5740d-f556-40fb-bc51-7a99b35630b3?

[xiiicool]

Дмитрий, Да, там в фильтре выбирается страна потом оператор потом может ещё пару динамически параметров

[xiiicool]

спасибо всем, буду пробовать

[Дмитрий]

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

[xiiicool]

Индексы по каждому полю а так же составной индекс не помог, вероятно из за MAX в условии функции приходится обходить все строки, на этом этапе индекс не подхватывает
добавил вычисление через WITH избавился двойного вычисления, помогло ускорить на 60%, но это всё равно пока долго ) ~57сек при 147т записей в базе этой категории

[Руслан .]

xiiicool, можно еще попробовать в cte убрать оконные функции и сделать

Select min(rr.endAt), max(rr.endAt)
..... 
group by rr.value, rr.rateTimemaskUUID, rr.billIncUUID, rr.notes, rr.name, rr.destinationUUID