Индексация нулевых значений в PostgreSQL
У меня есть запрос формы:
select m.id from mytable m
left outer join othertable o on o.m_id = m.id
and o.col1 is not null and o.col2 is not null and o.col3 is not null
where o.id is null
Запрос возвращает несколько сотен записей, хотя таблицы имеют миллионы строк, и он берет навсегда для выполнения (приблизительно час).
Когда я проверяю свое индексное использование статистики:
select * from pg_stat_all_indexes
where schemaname <> 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%')
Я вижу, что только индекс для othertable.m_id используется, и что индексы для col1.. 3 не используются вообще. Почему это?
Я читал в немногие места, что PG традиционно не смог индексировать Нулевые значения. Однако я читал, это, предположительно, изменилось начиная с PG 8.3? Я в настоящее время использую PostgreSQL 8.4 на Ubuntu 10.04. Я должен сделать "частичный" или "функциональный" индекс конкретно для ускорения, запросы NOT NULL или это, уже индексация АННУЛИРУЕТ, и я просто неправильно понимаю проблему?
5 ответов
Вы можете попробовать частичный индекс:
CREATE INDEX idx_partial ON othertable (m_id)
WHERE (col1 is not null and col2 is not null and col3 is not null);
Из документов: http://www.postgresql.org/docs/current/interactive/indexes-partial.html
Частичные индексы здесь вам не помогут, поскольку они найдут только те записи, которые вам не нужны. Вы хотите создать индекс, содержащий нужные вам записи.
CREATE INDEX findDaNulls ON othertable ((COALESCE(col1,col2,col3,'Empty')))
WHERE col1 IS NULL AND col2 IS NULL AND col3 IS NULL;
SELECT *
FROM mytable m
JOIN othertable o ON m.id = o.m_id
WHERE COALESCE(col1,col2,col3,'Empty') = 'Empty';
Кстати, поиск нулевых левых объединений обычно не так быстр, как использование EXISTS или NOT EXISTS в Postgres.
Частичный индекс здесь кажется правильным:
Если у вас есть таблица, содержащая оба выставленные и не выставленные счета, где неоплаченные заказы занимают небольшую доля от общей таблицы и все же это самые популярные строки, вы может повысить производительность, создав индексировать только неучтенные строки.
Возможно, эти столбцы, допускающие значение NULL (col1, col2, col3), действуют в вашем сценарии как своего рода флаг, чтобы различать какой-то подкласс записей в вашей таблице? (например, какое-то «логическое удаление»)? В этом случае, помимо решения с частичным индексом, вы могли бы предпочесть переосмыслить свой дизайн и поместить их в разные физические таблицы (возможно, используя наследование), одну для «живых записей», другую для «исторических записей» и получить доступ к полному набору. (только при необходимости) через вид.
Моей первой мыслью об этом запросе было бы создание единственного индекса для m_id, col1, col2 и o.col3.
И используйте EXPLAIN в этом запросе, чтобы увидеть, как он выполняется и что занимает так много времени. Вы можете показать нам результаты, чтобы помочь вам.
Вы пытались создать объединенный индекс для другой таблицы (m_id, col1, col2, col3)?
Вам также следует проверить план выполнения (используя EXPLAIN) вместо проверки использования индекса в системных таблицах.
PostgreSQL 9.0 (в настоящее время находится в стадии бета-тестирования) сможет использовать и индексировать условие IS NULL. Эта функция была отложена