Комментарии (25)

1. 

   Peter03

   29.05.2019 23:35

   #20219548

   Похоже данный подход не работает с stored procedures или вы их не используете?

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 10:28

      #20220706

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

1. 

   BlessYourHeart

   30.05.2019 05:42

   #20219948

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

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

   
   

   1. 

      VolCh

      30.05.2019 06:16

      #20219974

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

      
      

      1. 

         dsemyriazhko Автор

         30.05.2019 09:59

         #20220536

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

   
   

   1. 

      mad_nazgul

      30.05.2019 12:44

      #20221414

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

1. 

   VolCh

   30.05.2019 06:18

   #20219976

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

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 09:44

      #20220462

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

1. 

   IvanVakhrushev

   30.05.2019 11:06

   #20220922

   +2

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

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 11:52

      #20221158

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

      
      

      1. 

         sshikov

         30.05.2019 20:28

         #20223878

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

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 18:56

      #20223456

      Если я, например, добиваюсь идеального плана выполнения в тесте, например на 10К-20К записей. Потом на проде планировщик за счет статистики, по моему опыту, может сделать запрос только лучше, но я не припомню, чтобы он его прибил в ноль.
      
      Например, если планировщик увидит, что в результате фильтрации позвращается больше определенного процента записей, он может посчитать, что использовать seq scan оправданно. При этом время запроса только улучшится (в большенстве случаев).
      
      Мне же как разработчику важно изначально «подстелить соломку» и провести некую работу над запросом, убедившись, что планировщик вообще в состоянии его выполнить оптимально.
      
      Может я, конечно, не встречался еще с такими кейсами. Буду рад примеру.

1. 

   angel_zar

   30.05.2019 12:34

   #20221368

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

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 12:57

      #20221506

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

   
   

   1. 

      mayorovp

      30.05.2019 14:21

      #20221832

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

      
      

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

      
      

      1. 

         valery1707

         30.05.2019 14:34

         #20221926

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

         
         

         1. 

            mayorovp

            30.05.2019 17:10

            #20222850

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

         
         

         1. 

            sshikov

            30.05.2019 20:30

            #20223888

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

            
            

            1. 

               valery1707

               31.05.2019 12:17

               #20226424

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

               
               

               1. 

                  sshikov

                  31.05.2019 12:31

                  #20226474

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

1. 

   fuggy

   30.05.2019 22:20

   #20224254

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

   
   

   1. 

      dsemyriazhko Автор

      30.05.2019 22:27

      #20224280

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

1. 

   vpasechnik

   31.05.2019 13:30

   #20226836

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

1. 

   krupt

   31.05.2019 14:48

   #20227394

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

1. 

   pserega

   31.05.2019 15:34

   #20227666

   -1

   Если используете 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;
       }
   }
   

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