PostgreSQL Daterange неправильно использует индекс

Question

У меня есть простая таблица, которая имеет поле user_birthday с типом даты (который может быть значением NULL)

CREATE TABLE users
(
  user_id bigserial NOT NULL,
  user_email text NOT NULL,
  user_password text,
  user_first_name text NOT NULL,
  user_middle_name text,
  user_last_name text NOT NULL,
  user_birthday date,
  CONSTRAINT pk_users PRIMARY KEY (user_id)
)

В этом поле определен индекс (btree) с правилом NOT user_birthday IS NULL.

CREATE INDEX ix_users_birthday
  ON users
  USING btree
  (user_birthday)
  WHERE NOT user_birthday IS NULL;

Пытаясь развить другую идею, я добавил расширение btree_gist и создал следующий индекс:

CREATE INDEX ix_users_birthday_gist
  ON glances.users
  USING gist
  (user_birthday)
  WHERE NOT user_birthday IS NULL;

Но это также не повлияло, так как из того, что я мог прочитать, оно не используется для проверки диапазона.

Версия PostgreSQL - 9.3.4.0 (22) Postgres.app, и проблема также существует в 9.3.3.0 (21) Postgres.app

Меня заинтриговали следующие запросы:

Запрос № 1:

EXPLAIN ANALYZE SELECT *
FROM users
WHERE user_birthday <@ daterange('[1978-07-15,1983-03-01)')

Запрос № 2:

EXPLAIN ANALYZE SELECT *
FROM users
WHERE user_birthday BETWEEN '1978-07-15'::date AND '1983-03-01'::date

которые, на первый взгляд, должны иметь один и тот же план выполнения, но по некоторым причинам вот результаты:

Запрос № 1:

"Seq Scan on users  (cost=0.00..52314.25 rows=11101 width=241) (actual
time=0.014..478.983 rows=208886 loops=1)"
"  Filter: (user_birthday <@ '[1978-07-15,1983-03-01)'::daterange)"
"  Rows Removed by Filter: 901214"
"Total runtime: 489.584 ms"

Запрос № 2:

"Bitmap Heap Scan on users  (cost=4468.01..46060.53 rows=210301 width=241)
(actual time=57.104..489.785 rows=209019 loops=1)"
"  Recheck Cond: ((user_birthday >= '1978-07-15'::date) AND (user_birthday
<= '1983-03-01'::date))"
"  Rows Removed by Index Recheck: 611375"
"  ->  Bitmap Index Scan on ix_users_birthday  (cost=0.00..4415.44
rows=210301 width=0) (actual time=54.621..54.621 rows=209019 loops=1)"
"        Index Cond: ((user_birthday >= '1978-07-15'::date) AND
(user_birthday <= '1983-03-01'::date))"
"Total runtime: 500.983 ms"

Как видите, <@ daterange не использует существующий индекс, в то время какBETWEEN делает.

Важно отметить, что фактический вариант использования этого правила находится в более сложном запросе, который не приводит к проверке перепроверки Cond и Bitmap Heap. В сложном запросе приложения разница между двумя методами (с 1,2 миллионами записей) огромна: запрос № 1 при 415 мс, запрос № 2 при 84 мс.

Это ошибка с daterange? Я делаю что-то неправильно? или же datarange <@ выступает на дизайн?

Также есть обсуждение в списке рассылки pgsql-bugs

Answer 1

BETWEEN включает в себя верхнюю и нижнюю границу. Ваше состояние

WHERE user_birthday BETWEEN '1978-07-15'::date AND '1983-03-01'::date

Матчи

WHERE user_birthday <@ daterange('[1978-07-15,1983-03-01]')

Я вижу, вы упомянули индекс btree. Для этого используйте простые операторы сравнения.

Подробная страница справочника о том, какой индекс подходит для каких операторов.

Операторы типа диапазона <@ или же @> будет работать с индексами GiST.
Пример:
Выполните запрос часов этой работы в PostgreSQL