Как заставить Postgres использовать индекс для набора значений?
У меня есть таблица с примерно 35 миллионами строк, и я пытаюсь найти «обработанные» записи, которые можно время от времени удалять. Действующих статусов 14, из них 10 обрабатываются.
id uuid default uuid_generate_v4() not null primary key,
fk_id uuid not null references fk_table,
-- ... other columns
created_date timestamptz default now() not null,
status varchar(128) not null
Значения статуса могут быть одним из a,b,c,d,e,f,g,h,i,j,k,l,m,n (14)
Индекс включен (status,created_date).
Запрос типа:
select id from table
where created_date < 'somedate'
and status = ANY('{a,b,c,d,e,f,g,h,i,j}') -- one of first 10 values
Планировщик запросов настаивает на использовании полного seq_scan вместо индекса.
Есть ли способ заставить Postgres использовать индекс для части предиката status = ANY?
@FrankHeikens Я посмотрю, что смогу сделать.
Использует ли он индекс, если вы используете синтаксис IN ( … )?
@Bergi нет, в любом случае он не использует индекс.
@Bergi, в ответе Эрвина Брандштеттера есть отличная SQL-скрипт, демонстрирующая различия в поведении массива.
Ответы 2
Если более нескольких процентов строк соответствуют критериям (или, скорее, если Postgres оценивает это количество), он выберет последовательное сканирование, которое в таком случае быстрее.
Если на самом деле соответствует требованиям только несколько строк, то статистика вашего столбца (и/или настройки стоимости) виновата в вводящих в заблуждение оценках.
Если указанный индекс предназначен только для конкретных целей и только относительно небольшое количество строк имеют «обработанное» состояние, замените его частичным индексом:
CREATE INDEX foo ON tbl (created_date) WHERE status = ANY('{a,b,c..10}')
Индекс станет намного меньше, запрос будет быстрее, а вероятность его использования увеличится.
В любом случае, увеличение целевой статистики для created_date и status хотя бы немного, скорее всего, будет полезным. Видеть:
И более агрессивные autovacuum настройки стола:
В любом случае, только для «14 действительных состояний» status varchar(128) кажется чрезвычайно расточительным.
Кроме того, планировщик в Postgres 16 стал умнее по сравнению с Postgres 12. Postgres 16 обнаруживает общие значения даже во входных массивах с большим количеством элементов (намного больше, чем у вас есть отдельные значения в status), и соответствующим образом переключает план. Не уверен насчет старой логики в Postgres 12, но более чем? элементы в массиве, Postgres использовал для переключения на общие оценки, что может привести к плохим результатам.
Но обратите внимание, что любая версия может даже адаптировать план для подготовленных утверждений на основе фактических данных.
рабочий пример -- стр. 16
рабочий пример -- стр. 12
Дальше зависит от недостающих деталей...
Может ли postgres на самом деле выполнить оценку, если сравниваемое значение является массивом, знает ли он длину и элементы массива? Что, если значение массива будет передано как параметр (а не как литерал SQL в тексте запроса)?
@Bergi значение массива передается как параметр
@JJS Это было мое предположение, и я боюсь, что это одна из вещей, которые могут повлиять на планировщик, поэтому спасибо за разъяснение этой детали. Будучи динамическим подмножеством состояний, безусловно, делает недействительной идею использования частичного индекса.
@Erwin Brandstetter Я бы подумал, что задействованные предикаты смогут эффективно использовать индекс. Это потому, что созданный план запроса является общим и не понимает значения параметров? Должен ли я включить это в свой первоначальный вопрос? > и лишь относительно небольшое количество строк имеют «обработанное» состояние, замените его частичным индексом: я попробовал это и не получил лучших результатов. Возможно, я сделал что-то не так. Я прочитаю предоставленные ссылки.
Postgres может использовать оценки элементов массива во входных данных с некоторыми ограничениями. Подготовленный оператор более ограничен, но он все равно может адаптировать план к входным данным. Однако Postgres 16 справляется с этим намного лучше, чем Postgres 12. я добавил чуть выше
@ErwinBrandstetter спасибо за эту поправку и дополнительные мысли. Это дает мне дополнительные мысли, которые мне нужны, а также дает мне мотивацию перейти с PG12 на 16! >Больше зависит от недостающих деталей... Извините, я не смог предоставить больше. Вы фантастически справились с тем, что я предоставил.
Вероятно, он просто думает, что последовательное сканирование будет быстрее, и, насколько нам известно, он прав в том, что оно быстрее. Вы можете заставить его попробовать оба плана, изменив настройку «enable_seqscan» и получив EXPLAIN (ANALYZE, BUFFERS) под каждой настройкой. Таким образом, мы сможем увидеть, какой из них на самом деле быстрее (запустите его несколько раз в каждом направлении, чтобы убедиться, что для каждого из них получено согласованное время, а не просто однократная случайность), и каковы его предполагаемые и подсчитанные строки, чтобы увидеть, являются ли они противоречивы.
Если существует корреляция столбцов, так что в этих 10 статусах выборочно отсутствует низкое значение созданной_даты, этот перекос может сделать невозможным получение точных оценок количества строк. Этот тип перекоса вполне вероятен, учитывая ваше описание того, как строки удаляются из таблицы. И ни один из реализованных в настоящее время типов расширенной статистики, вероятно, не сможет решить проблему такого типа оценки.
Но независимо от этого, если вы создадите правильный индекс, чтобы он мог использовать сканирование только индекса, (status,created_date,id), он на самом деле должен быть быстрее и также должен быть оценен как более быстрый, и поэтому, скорее всего, будет использовать этот индекс, даже если оценки строки оставаться неправым. Это с большей вероятностью сработает, чем частичный индекс, предложенный Эрвином, потому что (увы) PostgreSQL не использует размер частичных индексов в процессе оценки. Таким образом, даже если индекс может быть небольшим, PostgreSQL не будет использовать эти знания для использования частичного индекса.
Я выздоравливающий разработчик MSSQL. В этом механизме все некластеризованные индексы неявно содержат ключевые столбцы кластеризованного индекса для этой строки как часть своего определения. Вы хотите сказать, что в postgres ключ кластеризованного индекса не является неявной частью определения некластеризованного индекса, и вам нужно явно включить его? Я уверен, что некластеризованный индекс должен будет включать как минимум ctid, но мне кажется, что вы здесь говорите о том, что после сканирования некластеризованного индекса у него есть список ctid, который он должен вернитесь в кучу, чтобы обменять ctid. Верно?
это золото. можно ссылку на источник? > PostgreSQL не использует размер частичных индексов в процессе оценки. Я считаю, что MSSQL заботится о размере индекса (страниц или общий размер, не помню)
@JJS PostgreSQL не имеет кластеризованных индексов (по крайней мере, в том смысле, в котором MSSQL использует этот термин). Все индексы вторичны. Первичный ключ не является автоматически частью каждого индекса. Если вы хотите иметь возможность извлекать столбец первичного ключа из индекса, вам необходимо явно включить его в индекс. Так что да, в противном случае ему необходимо посетить таблицу, чтобы «обменять» ctid на ПК.
@JSS Я не думаю, что для этого есть источник. Просто не хватает источника. То есть исходного кода, использующего эту информацию, не существует, несмотря на то, что он доступен. Нереализованные функции обычно не документируются, если только это не необходимо для документирования отклонения от стандарта, но здесь это не применимо. Размер индекса используется для оценки затрат на ввод-вывод, а не для оценки строк.
> Спасибо, в PostgreSQL нет кластерных индексов. Мне еще многое предстоит узнать о том, чем PostgreSQL отличается от MSSQL. Можете ли вы указать мне, где описывается, каким целям служит первичный ключ и как он влияет на порядок кучи?
Не могли бы вы поделиться результатами
explain(analyze, verbose, buffers, settingsи полным DDL для всех задействованных таблиц и индексов? Все в виде обычного текста, как обновление исходного вопроса.