Индексирование нулевых значений в 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 традиционно не смог индексировать значения NULL. Однако, я читал, что это, возможно, изменилось с PG 8.3? В настоящее время я использую PostgreSQL 8.4 на Ubuntu 10.04. Мне нужно сделать "частичный" или "функциональный" индекс специально для ускорения запросов NOT NOT, или это уже индексирование NULL, и я просто не понимаю проблему?

Ответ 1

Вы можете попробовать частичный индекс:

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

Ответ 2

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

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';

BTW ищет нулевые левые соединения, как правило, не так быстро, как использование EXISTS или NOT EXISTS в Postgres.

Ответ 3

Единственным показателем для m_id, col1, col2 и o.col3 будет моя первая мысль для этого запроса.

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

Ответ 4

A частичный индекс кажется правильным здесь:

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

Возможно, эти столбцы с нулевым значением (col1, col2, col3) действуют в вашем сценарии как некоторый флаг, чтобы отличить некоторый подкласс записей в вашей таблице? (например, какое-то "логическое удаление" )? В этом случае, помимо решения частичного индекса, вы можете предпочесть переосмыслить свой дизайн и поместить его в разные физические таблицы (возможно, используя наследование), один для "живых записей", другой для "исторических записей", и получить доступ к полному набору (только при необходимости).

Ответ 5

Вы пытались создать объединенный индекс на другой таблице (m_id, col1, col2, col3)?

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

PostgreSQL 9.0 (в настоящее время в бета-версии) сможет использовать и индексировать условие IS NULL. Эта функция отложена