Как оптимизировать запрос через Sqlalchemy?

Question

[WishesFire]

query = (
            select(
                func.coalesce(A.id, "").label("card_1"),
                func.coalesce(A.number, "").label("card_2"),
                func.coalesce(B.name, "").label("card_3"),
                func.coalesce(C.name, "").label("card_4"),
                func.coalesce(A.name, "").label("card_5"),
                func.coalesce(A.phone, "").label("card_6"),
                func.coalesce(A.balance, "").label("card_7"),
                func.coalesce(A.balance, "").label("card_8"),
                func.coalesce(A.active, "").label("card_9"),
                func.coalesce(D.name, "0").label("card_10"),
                func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None,
            ).filter(
                A.company_id.in_(filter_body.prj_ids)
            ).join(
                B, A.company_id == B.id
            ).join(
                C, A.voc_id == C.id
            ).outerjoin(
                D, A.plan == D.id
            ).offset(filter_body.offset).limit(filter_body.limit)
        )
        async with self._async_session_scope() as s:
            result = await s.execute(query)
        return result.fetchall()

У меня есть такой запрос (label название заменил), он с большим количеством запросов работает очень долго, несколько тысяч записей вытягивается более минуты. Если даже брать за раз не все данные на базе, а брать по 1000 например, через limit и offset. Может ли кто-нибудь посоветовать какие можно сделать оптимизации по коду?

Comments

[chemdev]

Может ли кто-нибудь посоветовать какие можно сделать оптимизации по коду?

скажи структуру таблиц, на первый взгляд тут слишком много coaelse. Зачем это?
А в остальном можно сравнить explain если фильтр перетащить в join.
Чтобы тебе именно по оптимизации запроса помогли, вытащи из orm формируемый sql-запрос и прикрепи к вопросу.

[WishesFire]

chemdev,

Может coalesce оказывать влияние на производительность? Сделано оно для этого, чтобы удобно валидировать данные как дефолтное значение. Фильтр по сути с главной таблицы идет, от которой join идет. По структуре есть все эти поля. У них нет relationship, только id. Меня интересует ли использование orm relationship как ForeignKey, даст больше производительность?

Запрос:

'SELECT coalesce(A.id, :coalesce_1) AS card_id, coalesce(A.serial_number, :coalesce_2) AS card_number, coalesce(B.name, :coalesce_3) AS prj_name, coalesce(C.name, :coalesce_4) AS client_name, coalesce(A.user_name, :coalesce_5) AS card_owner_name, coalesce(A.user_phone_number, :coalesce_6) AS card_owner_phone_number, coalesce(A.balance_current, :coalesce_7) AS current_balance, coalesce(A.balance_bonus, :coalesce_8) AS bonus_balance, coalesce(A.active, :coalesce_9) AS card_status, coalesce(D.name, :coalesce_10) AS tariff_plan_name, NULL AS anon_1 \nFROM cashless_card JOIN company ON cashless_card.company_id = company.id JOIN voc ON cashless_card.voc_id = voc.id LEFT OUTER JOIN tariff_plan ON cashless_card.tariff_plan = tariff_plan.id WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1])'

[chemdev]

Может coalesce оказывать влияние на производительность?

да, ведь это дополнительные вычисления, другое дело понять, является ли это узким горлышком.
Приведи create table для таблиц.
Также хочу отметить, что необходимость преобразования каждого столбца намекает на ошибку проектирования. Я не понял значение этих фраз:

удобно валидировать данные как дефолтное значение

У них нет relationship, только id.

[WishesFire]

chemdev,

я имел в виду что у самих табличек через orm, нет
relationship('A', foreign_keys=[a_id], primaryjoin='A.id == B.currency_id'), вместо этого есть только поле Integer, в котором записана id.

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

[chemdev]

WishesFire,

Значение может прийти null с какого-то поля, и вместо того, чтобы его показывать, используется coalesce, чтобы показать пустую стрингу

Зачем это делать на бэкенде?
Приведи create table для таблиц.

[Akina]

WishesFire,

Может coalesce оказывать влияние на производительность?

Оно, конечно, оказывает. Но настолько незначительное, что этим можно смело пренебречь, а в большинстве случаев даже не получится устойчиво детектировать - эффект от функции будет меньше точности измерения.

[Akina]

chemdev,

Также хочу отметить, что необходимость преобразования каждого столбца намекает на ошибку проектирования.

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

Answer 1

[Akina]

Если отформатировать запрос, чтобы было видно, что происходит, получим следующее:

SELECT coalesce(A.id, :coalesce_1) AS card_id, 
       coalesce(A.serial_number, :coalesce_2) AS card_number, 
       coalesce(B.name, :coalesce_3) AS prj_name, 
       coalesce(C.name, :coalesce_4) AS client_name, 
       coalesce(A.user_name, :coalesce_5) AS card_owner_name, 
       coalesce(A.user_phone_number, :coalesce_6) AS card_owner_phone_number, 
       coalesce(A.balance_current, :coalesce_7) AS current_balance, 
       coalesce(A.balance_bonus, :coalesce_8) AS bonus_balance, 
       coalesce(A.active, :coalesce_9) AS card_status, 
       coalesce(D.name, :coalesce_10) AS tariff_plan_name, 
       NULL AS anon_1 
FROM cashless_card 
JOIN company ON cashless_card.company_id = company.id 
JOIN voc ON cashless_card.voc_id = voc.id 
LEFT OUTER JOIN tariff_plan ON cashless_card.tariff_plan = tariff_plan.id 
WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1])

Почти очевидно, что в таблицах company, voc и tariff_plan поле по имени id - это первичный ключ... но хотелось бы подтверждения.

Впрочем, видно, что для оптимизации запроса в таблице cashless_card крайне желательно наличие индекса (company_id). Следует также проверить эффективность индекса (company_id, voc_id, tariff_plan), и аналогичного с переставленными местами последними 2 полями (первым ставить то, которое более селективно).

Кроме того, эффективность запроса сильно зависит от количества идентификаторов в списке, который передаётся в условие WHERE cashless_card.company_id IN (__[POSTCOMPILE_company_id_1]). Если их более десятка, есть смысл преобразовать этот список в набор данных (синтетическая UNION-таблица) и использовать как ещё один источник данных запроса.

Ну и надо понимать, что наличие LIMIT/OFFSET только замедляет выполнение запроса. Причём чем больше OFFSET, тем сильнее это замедление.

Comments

[WishesFire]

id это первичный ключ. индексы есть до этих полей

C offset это лучшее решение, чем вытаскивать все записи за один запрос

[Akina]

WishesFire,

индексы есть до этих полей

Это вот к чему сказано? Три индекса по отдельным полям и один композитный по трём полям - это две большие разницы. И я указываю на необходимость именно композитного индекса, ибо одна копия таблицы-источника не может использовать более чем один индекс, соответственно три отдельных индекса бессмысленны, использоваться может лишь один из них.

C offset это лучшее решение, чем вытаскивать все записи за один запрос

Ну тут хозяин - барин. Если на каждое листание идёт новый запрос, с другим OFFSET - ну, наверное, да. Хотя можно было бы подумать о выборке в temporary table и дальнейший просмотри исключительно через неё, будет куда как быстрее. А если запрос выполняется единожды, а потом листается кэшированное локально содержимое - то вовсе даже не очевидно.

[WishesFire]

Akina,

Меня больше интересует использование union, список фильтрации большой, как это лучше сделать?
там просто список id, [1, 2, 3, ...]

[WishesFire]

Akina,

project_queries = []
        for project_id in filter_body.prj_ids:
            project_query = query.filter(CashlessCard.company_id == project_id)
            project_queries.append(project_query)
        final_query = union(*project_queries)

задумка такая?

[Akina]

WishesFire,

Меня больше интересует использование union, список фильтрации большой, как это лучше сделать?
там просто список id, [1, 2, 3, ...]

В результате вместо WHERE cashless_card.company_id IN (1,2,3)должно получиться

...
INNER JOIN ( SELECT 1 AS company_id UNION ALL
             SELECT 2 UNION ALL
             SELECT 3
             ) AS criteria ON cashless_card.company_id = criteria.company_id

А уж как это отобразить в твоём фреймворке- я лично фиг знает.

Ещё вариант - создать temporary table, слить в неё список и INNER JOIN этой таблицы. Дополнительный профит - эту таблицу можно проиндексировать.

[o5a]

WishesFire, откуда вообще берется filter_body? Сколько записей в filter_body.prj_ids когда тот запрос начинает так сильно тормозить, и сколько записей всего в основной таблице?

Answer 2

[Адриан Макриденко]

1. Использовать индексы https://docs.sqlalchemy.org/en/21/core/metadata.ht...

2. Выбор только необходимых столбцов это достигается через `select`
https://docs.sqlalchemy.org/en/21/orm/query.html#s...

3. Можно птимизация использования функций:

Функции, используемые в запросах, могут сделать их медленными. Проверьте, какие функции вы используете и как они влияют на производительность. В SQLAlchemy это функции из модуля `func`.

https://docs.sqlalchemy.org/en/21/core/sqlelement....

Тоже будет полезно:

https://docs.sqlalchemy.org/en/21/orm/query.html#s...
https://docs.sqlalchemy.org/en/21/faq/performance....

query = (
    select(
        func.coalesce(A.id, "").label("card_1"),
        func.coalesce(A.serial_number, "").label("card_2"),
        func.coalesce(B.name, "").label("card_3"),
        func.coalesce(C.name, "").label("card_4"),
        func.coalesce(A.user_name, "").label("card_5"),
        func.coalesce(A.user_phone_number, "").label("card_6"),
        func.coalesce(A.balance_current, "").label("card_7"),
        func.coalesce(A.balance_bonus, "").label("card_8"),
        func.coalesce(A.active, "").label("card_9"),
        func.coalesce(D.name, "0").label("card_10"),
        func.coalesce(A.type, "").label("card_11") if filter_body.is_active == 1 else None,
    )
    .filter(A.company_id.in_(filter_body.prj_ids))
    .join(B, A.company_id == B.id)
    .join(C, A.voc_id == C.id)
    .outerjoin(D, A.tariff_plan == D.id)
    .offset(filter_body.offset)
    .limit(filter_body.limit)
)

async with self._async_session_scope() as s:
    result = await s.execute(query)
return result.fetchall()