Запретить использование индекса для определенного запроса в Postgres

У меня медленный запрос в базе данных Postgres. С помощью explain analyzeЯ вижу, что Postgres выполняет сканирование растровых индексов по двум различным индексам, а затем растровое И по двум результирующим наборам.

Удаление одного из индексов делает оценку в десять раз быстрее (сканирование индекса по растровому изображению все еще используется для первого индекса). Однако этот удаленный индекс полезен в других запросах.

Запрос:

select
  booking_id
from
  booking
where
  substitute_confirmation_token is null
  and date_trunc('day', from_time) >= cast('01/25/2016 14:23:00.004' as date)
  and from_time >= '01/25/2016 14:23:00.004'
  and type = 'LESSON_SUBSTITUTE'
  and valid
order by
  booking_id;

Индексы:

"idx_booking_lesson_substitute_day" btree (date_trunc('day'::text, from_time)) WHERE valid AND type::text = 'LESSON_SUBSTITUTE'::text
"booking_substitute_confirmation_token_key" UNIQUE CONSTRAINT, btree (substitute_confirmation_token)

План запроса:

Sort  (cost=287.26..287.26 rows=1 width=8) (actual time=711.371..711.377 rows=44 loops=1)
  Sort Key: booking_id
  Sort Method: quicksort  Memory: 27kB
  Buffers: shared hit=8 read=7437 written=1
  ->  Bitmap Heap Scan on booking  (cost=275.25..287.25 rows=1 width=8) (actual time=711.255..711.294 rows=44 loops=1)
        Recheck Cond: ((date_trunc('day'::text, from_time) >= '2016-01-25'::date) AND valid AND ((type)::text = 'LESSON_SUBSTITUTE'::text) AND (substitute_confirmation_token IS NULL))
        Filter: (from_time >= '2016-01-25 14:23:00.004'::timestamp without time zone)
        Buffers: shared hit=5 read=7437 written=1
        ->  BitmapAnd  (cost=275.25..275.25 rows=3 width=0) (actual time=711.224..711.224 rows=0 loops=1)
              Buffers: shared hit=5 read=7433 written=1
              ->  Bitmap Index Scan on idx_booking_lesson_substitute_day  (cost=0.00..20.50 rows=594 width=0) (actual time=0.080..0.080 rows=72 loops=1)
                    Index Cond: (date_trunc('day'::text, from_time) >= '2016-01-25'::date)
                    Buffers: shared hit=5 read=1
              ->  Bitmap Index Scan on booking_substitute_confirmation_token_key  (cost=0.00..254.50 rows=13594 width=0) (actual time=711.102..711.102 rows=2718734 loops=1)
                    Index Cond: (substitute_confirmation_token IS NULL)
                    Buffers: shared read=7432 written=1
Total runtime: 711.436 ms

Можно ли запретить использование определенного индекса для определенного запроса в Postgres?

Источник

1 ответ

Решение

Ваше умное решение

Вы уже нашли умное решение для вашего конкретного случая: частичный уникальный индекс, который охватывает только редкие значения, поэтому Postgres не будет (не может) использовать индекс для общего NULL значение.

CREATE UNIQUE INDEX booking_substitute_confirmation_uni
ON booking (substitute_confirmation_token)
WHERE substitute_confirmation_token IS NOT NULL;

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

Наконец, частичный индекс также может использоваться для переопределения выбора плана запроса системы. Кроме того, наборы данных с особым распределением могут привести к тому, что система будет использовать индекс, когда это действительно не нужно. В этом случае индекс может быть настроен так, чтобы он не был доступен для ошибочного запроса. Обычно PostgreSQL делает разумный выбор в отношении использования индекса (например, он избегает их при извлечении общих значений, поэтому предыдущий пример действительно сохраняет только размер индекса, он не обязан избегать использования индекса), и крайне неправильный выбор плана является причиной ошибки отчет.

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

Вы прокомментировали: The table has few millions of rows and just few thousands of rows with not null values, так что это идеальный вариант использования. Это даже ускорит запросы на ненулевые значения для substitute_confirmation_token потому что индекс сейчас намного меньше.

Ответ на вопрос

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

Поддельный индекс падения

Вы можете удалить индекс внутри транзакции, запустить SELECT а затем, вместо совершения, использовать ROLLBACK, Это быстро, но учтите, что ( согласно документации):

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

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

BEGIN;
DROP INDEX big_user_id_created_at_idx;
SELECT ...;
ROLLBACK;  -- so the index is preserved after all

Более подробная статистика

Обычно, однако, этого должно быть достаточно, чтобы поднять STATISTICS цель для столбца, поэтому Postgres может более надежно определить общие значения и избежать индекса для них. Пытаться:

ALTER TABLE booking ALTER COLUMN substitute_confirmation_token SET STATISTICS 2000;

Затем: ANALYZE booking; прежде чем повторить запрос. 2000 является примером значения. Связанные с:

Источник
Другие вопросы по тегам