Hacker News Digest

SQLite использует составные индексы слева направо, без пропусков, останавливаясь на первом диапазонном условии. Например, если индекс включает столбцы A, B, C, а запрос содержит A BETWEEN x AND y AND B = z, оптимизатор сможет использовать только A для фильтрации по диапазону, затем просканирует все подходящие строки для B, игнорируя C. Это объясняет, почему добавление отдельных индексов на каждый столбец бесполезно — SQLite редко объединяет их.

Ключевой вывод: порядок столбцов в индексе критичен. Первым должен идти самый селективный столбец, но если на нём используется диапазон (например, BETWEEN или <=), последующие столбцы в индексе не будут эффективно фильтроваться. Для запросов с несколькими условиями лучше создать индекс, где диапазонные условия стоят последними, чтобы максимизировать использование индекса.

• Критика статьи за отсутствие технической глубины и общие заблуждения о работе индексов, не специфичных для SQLite.
• Обсуждение ментальных моделей индексов как вложенных карт или отсортированных списков, объясняющих важность порядка колонок и ограничения диапазонных запросов.
• Замечания о простоте планировщика запросов SQLite по сравнению с другими СУБД, но признание его адекватности для базовых сценариев.
• Рекомендации по использованию официальной документации SQLite и инструментов вроде .expert для анализа индексов.
• Предостережения против автоматического добавления индексов из-за затрат на дисковое пространство и замедления записи.

Оператор OR в SQL-запросах может быть неожиданно дорогим из-за сложностей планирования запросов. Например, запрос с OR для двух столбцов с индексами может выполняться более 100 мс на миллионе записей, в то время как эквивалентный запрос с использованием AND и подзапросов сокращает время до менее 1 мс. Это происходит потому, что оптимизатору сложно эффективно объединять результаты по индексам для условий OR, особенно при наличии дополнительных фильтров или сортировок.

Практическое решение — избегать OR в пользу денормализации данных. Например, вместо хранения нескольких внешних ключей в одной таблице можно создать отдельную таблицу связей, что упрощает запросы и ускоряет их выполнение за счёт линейных соединений. Это особенно важно для часто используемых операций, таких как поиск с множественными условиями.

• Обсуждаются проблемы производительности SQL-запросов с оператором OR, особенно при использовании предикатов по разным колонкам, и предлагается ручная оптимизация через переписывание в UNION ALL.
• Поднимается вопрос о сложности работы оптимизатора запросов, который может неправильно оценить количество строк из-за устаревшей статистики, что приводит к резкому росту сложности выполнения.
• Упоминаются различные техники индексирования (например, ESR для MongoDB) и важность правильного проектирования таблиц и индексов для избежания проблем с производительностью.
• Отмечается, что ORM часто генерируют неоптимальные запросы, и подчеркивается необходимость ручной проверки и настройки SQL, особенно в высоконагруженных системах.
• Обсуждается возможность применения машинного обучения и расширенной статистики в оптимизаторах запросов для улучшения оценки кардинальности и выбора более эффективных планов выполнения.

Проблема
При промахе кэша Readyset выполняет запрос с нуля. Для «расколотых» соединений (предикаты есть и в WHERE, и в ON) старый hash-join читал обе таблицы полностью:

• фильтр по email возвращал 1 строку users;
• фильтр по status='SHIPPED' — 99 % таблицы orders.
  Материализовав миллионы лишних строк, система строила хэш-таблицу и лишь потом отбрасывала ненужное. Профилирование показало: 30 % времени уходило на распаковку RocksDB, затем на 10 K IOPS и 81 % загрузки NVMe.

Решение: Index Condition Pushdown
Новый план использует индексы и «проталкивает» условия вниз:

1. Сначала выбираем 1 строку users по индексу email.
2. Для каждой найденной u.id делаем индексный lookup в orders с условием user_id = u.id AND status='SHIPPED'.
   Так читаются только нужные строки orders, объём I/O падает на порядки, а хэш-таблица больше не строится.

Результат
ICP устраняет лишнее чтение и распаковку, превращая холодный straddled-join из многомиллисекундной операции в субмиллисекундную.

• Пользователи жалуются, что планировщик запросов не продавливает фильтры к индексам, и считают это багом производительности.
• Многие разработчики вынуждены сами заниматься индексами, партиционированием и анализом EXPLAIN, потому что DBA лишь «держат свет».
• В RonDB и ReadySet показали примеры «pushdown-joins» и Index Condition Pushdown, дав ускорение до 450×.
• ReadySet описывают как «кеш-прокси» поверх MySQL/Postgres, который по логу репликации инкрементально обновляет материализованные представления.
• Часть участников считает, что современные оптимизаторы должны делать такие оптимизации автоматически, и не понимает, зачем изобретать велосипед.