Postgresql игнорирует индекс для столбца метки времени, даже если запрос выполняется быстрее с использованием индекса
На postgresql 9.3 у меня есть таблица с немногим более миллиона записей, таблица была создана как:
CREATE TABLE entradas
(
id serial NOT NULL,
uname text,
contenido text,
fecha date,
hora time without time zone,
fecha_hora timestamp with time zone,
geom geometry(Point,4326),
CONSTRAINT entradas_pkey PRIMARY KEY (id)
)
WITH (
OIDS=FALSE
);
ALTER TABLE entradas
OWNER TO postgres;
CREATE INDEX entradas_date_idx
ON entradas
USING btree
(fecha_hora);
CREATE INDEX entradas_gix
ON entradas
USING gist
(geom);
Я выполняю запрос для агрегирования строк по временным интервалам следующим образом:
WITH x AS (
SELECT t1, t1 + interval '15min' AS t2
FROM generate_series('2014-12-02 0:0' ::timestamp
,'2014-12-02 23:45' ::timestamp, '15min') AS t1
)
select distinct
x.t1,
count(t.id) over w
from x
left join entradas t on t.fecha_hora >= x.t1
AND t.fecha_hora < x.t2
window w as (partition by x.t1)
order by x.t1
Этот запрос занимает около 50 секунд. Из вывода команды объяснения видно, что индекс метки времени не используется:
Unique (cost=86569161.81..87553155.15 rows=131199111 width=12)
CTE x
-> Function Scan on generate_series t1 (cost=0.00..12.50 rows=1000 width=8)
-> Sort (cost=86569149.31..86897147.09 rows=131199111 width=12)
Sort Key: x.t1, (count(t.id) OVER (?))
-> WindowAgg (cost=55371945.38..57667929.83 rows=131199111 width=12)
-> Sort (cost=55371945.38..55699943.16 rows=131199111 width=12)
Sort Key: x.t1
-> Nested Loop Left Join (cost=0.00..26470725.90 rows=131199111 width=12)
Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
-> CTE Scan on x (cost=0.00..20.00 rows=1000 width=16)
-> Materialize (cost=0.00..49563.88 rows=1180792 width=12)
-> Seq Scan on entradas t (cost=0.00..37893.92 rows=1180792 width=12)
Тем не менее, если я делаю set enable_seqscan=false (Я знаю, это никогда не следует делать), тогда запрос выполняется менее чем за секунду, и вывод команды объяснения показывает, что он использует индекс в столбце метки времени:
Unique (cost=91449584.16..92433577.50 rows=131199111 width=12)
CTE x
-> Function Scan on generate_series t1 (cost=0.00..12.50 rows=1000 width=8)
-> Sort (cost=91449571.66..91777569.44 rows=131199111 width=12)
Sort Key: x.t1, (count(t.id) OVER (?))
-> WindowAgg (cost=60252367.73..62548352.18 rows=131199111 width=12)
-> Sort (cost=60252367.73..60580365.51 rows=131199111 width=12)
Sort Key: x.t1
-> Nested Loop Left Join (cost=1985.15..31351148.25 rows=131199111 width=12)
-> CTE Scan on x (cost=0.00..20.00 rows=1000 width=16)
-> Bitmap Heap Scan on entradas t (cost=1985.15..30039.14 rows=131199 width=12)
Recheck Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))
-> Bitmap Index Scan on entradas_date_idx (cost=0.00..1952.35 rows=131199 width=0)
Index Cond: ((fecha_hora >= x.t1) AND (fecha_hora < x.t2))
Почему Postgres не использует entradas_date_idx разве я заставлю его, даже если выполнение запроса намного быстрее с его использованием?
Как я могу использовать postgres entradas_date_idx не прибегая к set enable_seqscan=false?
4 ответа
Вы можете немного упростить свой запрос:
SELECT x.t1, count(*) AS ct
FROM generate_series('2014-12-02'::timestamp
, '2014-12-03'::timestamp
, '15 min'::interval) x(t1)
LEFT JOIN entradas t ON t.fecha_hora >= x.t1
AND t.fecha_hora < x.t1 + interval '15 min'
GROUP BY 1
ORDER BY 1;
DISTINCT в сочетании с оконной функцией обычно намного дороже (а также сложнее оценить) для планировщика запросов.
CTE не является необходимым и обычно более дорогим, чем подзапрос. А также сложнее оценить планировщик запросов, поскольку CTE являются барьерами для оптимизации.
Похоже, вы хотите охватить целый день, но вы пропустили последние 15 минут. Используйте более простой generate_series() выражение, чтобы покрыть весь день (все еще не совпадая с соседними днями).
Далее, почему у вас fecha_hora timestampwith time zone в то время как у вас также есть fecha date а также hora time [without time zone]? Похоже, так и должно быть fecha_hora timestamp и отбросить лишние столбцы?
Это также позволит избежать тонкой разницы с типом данных вашего generate_series() выражение - которое обычно не должно быть проблемой, но timestamp зависит от часового пояса вашего сеанса и не IMMUTABLE лайк timestamptz,
Если это не достаточно хорошо, добавьте избыточный WHERE условие, рекомендованное @Daniel для инструктирования планировщика запросов.
Базовый совет для плохих планов применим также:
Анализ неверной оценки
Суть проблемы заключается в том, что планировщик postgres не знает, какие значения и сколько строк выходят из generate_series звонить, и все же должен оценить, сколько из них будет удовлетворять условию JOIN против большого entradas Таблица. В вашем случае это не удастся.
В действительности, только небольшая часть таблицы будет объединена, но ошибки оценки на противоположной стороне, как показано в этой части ОБЪЯСНЕНИЯ:
-> Nested Loop Left Join (cost=0.00..26470725.90 rows=131199111 width=12)
Join Filter: ((t.fecha_hora >= x.t1) AND (t.fecha_hora < x.t2))
-> CTE Scan on x (cost=0.00..20.00 rows=1000 width=16)
-> Materialize (cost=0.00..49563.88 rows=1180792 width=12)
-> Seq Scan on entradas t (cost=0.00..37893.92 rows=1180792 width=12)
entradas оценивается в 1180792 строки, x оценивается в 1000 строки, которые я считаю, просто по умолчанию для любого вызова SRF. Результат ПРИСОЕДИНЕНИЯ оценивается в 131199111 строк, более чем в 100 раз больше строк большой таблицы!
Обмануть планировщика в лучшую оценку
Поскольку мы знаем, что временные метки в x принадлежат к узкому диапазону (один день), мы можем помочь планировщику с этой информацией в виде дополнительного условия JOIN:
left join entradas t
ON t.fecha_hora >= x.t1
AND t.fecha_hora < x.t2
AND (t.fecha_hora BETWEEN '2014-12-02'::timestamp
AND '2014-12-03'::timestamp)
(не имеет значения, что диапазон BETWEEN включает верхнюю границу или, как правило, немного больше, он будет строго отфильтрован другими условиями).
Затем планировщик должен иметь возможность использовать статистику, признать, что этот диапазон значений касается только небольшой части индекса, и использовать индекс вместо последовательного сканирования всей большой таблицы.
Если ваша таблица новая и строки недавно добавлены, postgres может не собрать достаточно статистики для новых данных. Если это так, вы можете попытаться проанализировать таблицу.
PS: убедитесь, что цель статистики не установлена на ноль в таблице.
С точки зрения использования индексов планировщик запросов пытается сделать обоснованное предположение (основываясь, среди прочего, на доступных индексах, статистике таблиц и самом запросе) о наилучшем способе выполнения запроса. Есть случаи, когда он всегда будет выполнять последовательное сканирование, даже если использование индекса будет намного, намного быстрее. Просто в этом случае планировщик запросов не знает об этом (во многих случаях, особенно когда запрос будет возвращать много строк, последовательное сканирование выполняется быстрее, чем выполнение сканирования по индексу).
По сути, это пример случая, когда вы знаете свои данные для этого очень конкретного случая лучше, чем планировщик запросов (который должен иметь более общий, более широкий взгляд, охватывающий различные случаи и возможные входные данные).
В таких случаях, когда вы знаете, принуждение к использованию индекса через enable_seqscan=falseЯ не думаю, что есть проблема с его использованием. Я делаю это сам для некоторых конкретных случаев, так как в противном случае это приведет к огромному снижению производительности, и я знаю, что для этих определенных запросов принудительное использование индекса приводит к запросам, которые на несколько порядков быстрее.
Однако следует помнить о двух вещах:
Вы всегда должны обязательно включать последовательное сканирование сразу после запроса, иначе оно останется для остальной части соединения для всех других запросов, что, скорее всего, не то, что вам нужно. Если ваш запрос немного меняется, или если данные в таблице значительно увеличиваются, выполнение индексации может быть более быстрым, хотя это, безусловно, проверяемая вещь.
Использование CTE может оказать существенное влияние на способность планировщика запросов эффективно оптимизировать запрос. Я не думаю, что это суть проблемы в этом случае.