Как перестать забывать про индексы и начать проверять execution plan в тестах

[dsemyriazhko]

Не исключаю, что где то они необходимы, но я успешно избегаю их использование уже на протяжении нескольких лет). На сколько мне известно, EXPLAIN не работает с инструкцией CALL, поэтому текущий ExecutionPlanQuery не сможет получить план.

Я стараюсь всю логику держать в приложении, а БД использовать только в качестве хранилища. Так легче масштабироваться: накидал пару дополнительных нод и готово. С БД все не так просто, реплики и тд гораздо сложнее. Прибегаю к этому в последнюю очередь имхо.

[BlessYourHeart]

ищет преобразованный (HQL -> SQL) запрос в логах

Неужели HQL настолько проще (поддерживать/управлять) чем SQL, что лучше выучить HQL и героически побеждять всю эту HQL -> SQL и писать отдельные модули для проверки и потом модули для проверки проверок…

[VolCh]

Он и подобные QL не то, что проще или сложнее — они, при внешней схожести на SQL, другие семантически: они, по сути, работают не со строками таблиц, а с хранилищами объектов. Ну и в среднестатистических проектах вообще редко используются напрямую, просто внутренняя деталь реализации ORM.

[dsemyriazhko]

Все верно, но даже с nativeQuery бывает непросто воспроизвести все параметры и т.д. С конкретным тестом все становится проще.

[mad_nazgul]

spring data jpa.
Программист напишет в data jpa repository что то типа findByNameAndAge, а потом это «превратиться» в запрос.
Ну а если использовать Spring Data Jpa, там и до Query не далеко.
И только самые стойкие доходят до @ Query(nativeQuery=true)

[mikaakim]

Иногда JDBCTemplate намного лучше, чем огромное количество репов с Query(nativeQuery=true)

[VolCh]

Интересный подход. А о более низком уровне не думали? Чтоб не зависеть от основного языка проекта?

[dsemyriazhko]

До текущего момента не думал), я ведь этим решал свою проблему. Нужно подумать, но кроме портации, например, на .net ничего в голову не приходит.

[IvanVakhrushev]

Чтобы это действительно работало и имело смысл, вам для тестирования нужна копия\обезличенная копия production базы. Распределение данных и статистика имеют здесь ключевое значение.
Анализировать производительность и план исполнения запросов на вручную сгенерированной БД — это лукавство, которое в один прекрасный момент выйдет вам боком.

[dsemyriazhko]

в целом ничего не мешает поднять копию продакшин БД и прогонять эти тесты на ней. Результаты, конечно, точнее будут.

но даже в таком виде какие то явные косяки (например, забыл что-то) мне помогло устранить.

[sshikov]

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

И кстати… не совсем понял, что у вас за база (видимо PostgreSQL), но скажем в оракле есть очевидные случаи, когда создание лишнего индекса не того типа как раз тормозит запросы на пару-тройку порядков.

[dsemyriazhko]

Если я, например, добиваюсь идеального плана выполнения в тесте, например на 10К-20К записей. Потом на проде планировщик за счет статистики, по моему опыту, может сделать запрос только лучше, но я не припомню, чтобы он его прибил в ноль.

Например, если планировщик увидит, что в результате фильтрации позвращается больше определенного процента записей, он может посчитать, что использовать seq scan оправданно. При этом время запроса только улучшится (в большенстве случаев).

Мне же как разработчику важно изначально «подстелить соломку» и провести некую работу над запросом, убедившись, что планировщик вообще в состоянии его выполнить оптимально.

Может я, конечно, не встречался еще с такими кейсами. Буду рад примеру.

[angel_zar]

А так ли нужно на каждый чих создавать индекс?
Следующая проблема медленные DML и тут нужно будет искать не используемые индексы.

[dsemyriazhko]

100% на каждый чих этого делать не нужно. В разработке вообще все нужно делать осознанно, такая у нас профессия). Но я бы проверял все «чихи» (изменение запроса, добавление нового), если в таблице записи исчисляются в миллионах.

[mayorovp]

Всё-таки лучше создать лишний индекс чем не создать нужного.

Когда в таблице десяток индексов, очередной индекс замедлит DML всего на 10%, а вот отсутствие нужного индекса способно замедлить выборку в пару миллионов раз...

[valery1707]

Лишний индекс будет тормозить INSERT/UPDATE никак не участвуя в SELECT.
Сильно ли это лучше чем отсутствие индекса — по мне так тоже зависит от ситуации.

[mayorovp]

К ситуации вида "Проходит еще пару минут, прилетает письмо от DB-админов. Пишут, что время выполнения запросов к базе данных (далее БД) пробило все возможные границы и стремится в бесконечность" легко прийти пропустив всего один важный индекс, но её тяжело достичь добавлением ненужного индекса. Нужна целая куча ненужных индексов, чтобы начались заметные лаги.

[sshikov]

Берете оракл. Создаете реверсивный индекс. Пишете запрос с between, имеете тормоза, примерно эквивалентные фулл скану. На SELECT, ясное дело.

[valery1707]

В вашем варианте новый индекс, как я понимаю, используется в запросе — потому он его и тормозит.
Я же говорил про случай когда индекс вообще в запросе не участвует и я не знаю БД в которой такой индекс бы мешал SELECT-ам.
Но вообще я отвечал на другой комментарий в котором говорили что "лучше создать лишний индекс чем не создать нужный" и я отвечал в контексте того что "лишний индекс тоже мешать будет".

[sshikov]

Ну, я просто привел пример, что индекс (лишний) вполне может мешать селектам. Используется ли он в запросе? Конечно да, но проблема в том, что для автора запроса это может быть не вполне очевидно.

Ну и да, ради объективности, реверсивные индексы — они все-таки не совсем «стандартные», и в какой-то степени настроены на то, чтобы оптимизировать как раз вставки — и частично именно поэтому влияют и на селект.

[fuggy]

К чему устраивать парсинг explain, если он может отдавать через EXPLAIN (FORMAT XML/JSON/YAML) в нужном формате на выбор.
Я думаю что, если запросы требуют быстрого ответа для пользователя, то лучше ориентироваться не на наличие или отсутствие Seq Scan, а оценивать Total Cost по какому-то порогу. К тому же есть определённый класс запросов для которых выборка через Seq Scan будет быстрее, чем использовать Index Scan.

[dsemyriazhko]

Парсинг все равно нужен, чтобы разложить в объектную модель и дальше удобно с ней работать, делая проверки.

Вы правы, распарсить JSON намного проще, чем TEXT)). Я как-то это упустил. Спасибо за подсказку! Добавлю в план написать другую реализацию query & parser.

[vpasechnik]

В Oracle DB можно экспортнуть статистику с прода, и импортнуть локально (не уверен можно ли так сделать в Postgres). После чего можно просто проверять косты запросов обычным трешхолдом. (например тест зеленый если кост меньше 100).
Иногда full table access (seq scan) не является самым плохим вариантом плана.

[krupt]

Спасибо за статью и библиотеку, но есть предложение. Не хочется при assert завязываться на имя индекса — лучше завязаться на поля таблиц, которые используются в запросе и проверять наличие этих полей в используемом индексе. Типо, assertIsColumnExistsInIndexIntoExecutionPlan :-)

[pserega]

Если используете JPA репозиторием (Hibernate), за чем перехватчики запросов использовать и думать, какая база используется.

public class QueryUtils<T> {

    private static final Logger log = LoggerFactory.getLogger(QueryUtils.class);

    @Autowired
    EntityManagerFactory entityManagerFactory;

    @Autowired
    EntityManager em;

    private final Class<T> type;

    public QueryUtils() {
        this.type = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
    }

    public String getQueryString(Filter<T> filter)
    {
        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<T> query = builder.createQuery(type);
        Root<T> root = query.from(type);
        Predicate predicate = filter.toSpecification().toPredicate(root, query, builder);

        if (predicate != null) {
            query.where(predicate);
        }

        QueryImpl queryI = em.createQuery(query).unwrap(QueryImpl.class);
        Map<String, TypedValue> paramMap = queryI.getQueryParameters()
.getNamedParameters();
        log.debug("map param : "+ paramMap);
        String hql = queryI.getQueryString().replace("fetch","");
        QueryTranslatorFactory ast = new ASTQueryTranslatorFactory();
        QueryTranslator queryTranslator = ast.createQueryTranslator(
                hql,hql, Collections.EMPTY_MAP,(SessionFactoryImplementor) 
        entityManagerFactory.unwrap(SessionFactory.class),null);
        queryTranslator.compile(Collections.EMPTY_MAP, true );
        log.debug("Table Sql : "+ queryTranslator.getQuerySpaces());

        String nativeSql = queryTranslator.getSQLString();

        for (TypedValue value : paramMap.values()) {
        nativeSql = nativeSql.replaceFirst("\\?", "'"+value.getValue().toString()+"'");
}

        log.debug("hql : "+ queryTranslator.getQueryString());
        log.debug("NativeSql : "+ nativeSql);

        return nativeSql;
    }
}

Можно получить запрос, не вызывая его.
За статью спасибо.

[kznalp]

А что вы будете делать, план запроса с использованием Index Scan в итоге получается дороже чем план запроса с использование Seq Scan?

Например:
Gather (cost=1260.70..95478.20 rows=9075 width=132)
Parallel Seq Scan on lot_data l (cost=0.00..81776.67 rows=2602867 width=33)
План:

Gather  (cost=1260.70..95478.20 rows=9075 width=132)
  Workers Planned: 2
  ->  Hash Join  (cost=260.70..93544.23 rows=3781 width=132)
        Hash Cond: ((p."SOITECPARAM_NAME")::text = (gr."SOITECPARAM_NAME")::text)
        ->  Nested Loop  (cost=226.03..93480.69 rows=3781 width=103)
              ->  Hash Join  (cost=225.62..91800.64 rows=3781 width=79)
                    Hash Cond: (l."SHIPMENT_ID" = s."SHIPMENT_ID")
                    ->  Parallel Seq Scan on lot_data l  (cost=0.00..81776.67 rows=2602867 width=33)
                    ->  Hash  (cost=225.52..225.52 rows=8 width=58)
                          ->  Nested Loop  (cost=0.84..225.52 rows=8 width=58)
                                ->  Nested Loop  (cost=0.55..190.70 rows=21 width=28)
                                      ->  Index Scan using unique_pc on pc  (cost=0.27..8.31 rows=1 width=28)
                                            Index Cond: (("PC_NAME")::text = 'C1161-049-01'::text)
                                      ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.28..182.19 rows=21 width=8)
                                            Index Cond: ("PC_ID" = pc."PC_ID")
                                ->  Index Scan using shipment_pkey on shipment s  (cost=0.28..1.66 rows=1 width=30)
                                      Index Cond: ("SHIPMENT_ID" = sp."SHIPMENT_ID")
                                      Filter: (("SHIPMENT_DATE" >= '2018-11-16 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2019-05-15 00:00:00'::timestamp without time zone))
              ->  Index Scan using parameters_idx1 on parameters p  (cost=0.41..0.43 rows=1 width=32)
                    Index Cond: ("PARAMETER_ID" = l."PARAMETER_ID")
        ->  Hash  (cost=20.41..20.41 rows=1141 width=28)
              ->  Seq Scan on report_parameters_groups gr  (cost=0.00..20.41 rows=1141 width=28)

Nested Loop (cost=2.09..48229.75 rows=1390 width=132)
Index Scan using lot_data_pkey on lot_data l (cost=0.56..46927.24 rows=3142 width=33)
План:

Nested Loop  (cost=2.09..48229.75 rows=1390 width=132)
  ->  Nested Loop  (cost=1.81..47809.74 rows=1390 width=104)
        ->  Nested Loop  (cost=1.40..47192.09 rows=1390 width=79)
              Join Filter: (s."SHIPMENT_ID" = l."SHIPMENT_ID")
              ->  Nested Loop  (cost=0.84..225.57 rows=1 width=58)
                    ->  Nested Loop  (cost=0.55..190.87 rows=21 width=28)
                          ->  Index Scan using unique_pc on pc  (cost=0.27..8.30 rows=1 width=28)
                                Index Cond: (("PC_NAME")::text = 'C21264-024-03'::text)
                          ->  Index Only Scan using shipmentpc_idx1 on shipment_pc sp  (cost=0.28..182.36 rows=21 width=8)
                                Index Cond: ("PC_ID" = pc."PC_ID")
                    ->  Index Scan using shipment_pkey on shipment s  (cost=0.28..1.65 rows=1 width=30)
                          Index Cond: ("SHIPMENT_ID" = sp."SHIPMENT_ID")
                          Filter: (("SHIPMENT_DATE" >= '2019-04-16 00:00:00'::timestamp without time zone) AND ("SHIPMENT_DATE" <= '2019-05-16 00:00:00'::timestamp without time zone))
              ->  Index Scan using lot_data_pkey on lot_data l  (cost=0.56..46927.24 rows=3142 width=33)
                    Index Cond: ("SHIPMENT_ID" = sp."SHIPMENT_ID")
        ->  Index Scan using parameters_idx1 on parameters p  (cost=0.41..0.43 rows=1 width=33)
              Index Cond: ("PARAMETER_ID" = l."PARAMETER_ID")
  ->  Index Scan using report_parameters_groups_pkey on report_parameters_groups gr  (cost=0.28..0.30 rows=1 width=28)
        Index Cond: (("SOITECPARAM_NAME")::text = (p."SOITECPARAM_NAME")::text)

[dsemyriazhko]

мои действия: после создания метода в репозитории, который создает этот запрос, я напишу тест. Изучу план выполнения запроса в этом тесте. Пойму, что Seq Scan более предпочтительный и поставлю соответствующий assert.

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

Смысл статьи не в том, что лучше или хуже, а в том, что я призывал проверять execution plan до выкадки на прод. Инструмент, который я предложил, всего лишь призван помочь в этом вопросе.

[mayorovp]

Что-то я не вижу в вашем случае свидетельств того, что Index Scan в итоге получился дороже...