PostgreSQL 日期范围未正确使用索引

Question

>我有一个简单的表格，其中包含一个带有日期类型的user_birthday字段（可以是空值）

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），规则为NOTuser_birthday为空。

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 和位图堆扫描。在应用程序复杂查询中，两种方法（有 120 万条记录）之间的差异是巨大的：查询 #1 在 415ms 时查询 #2 在 84 毫秒。

这是日期范围的错误吗？我做错了什么吗？或者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]')

我看到你提到了一个树索引。为此，请使用简单的比较运算符。

详细的手册页，说明哪个索引对哪些运算符有用。

范围类型运算符<@或@>将用于 GiST 索引。
例：
在 PostgreSQL 中执行此小时操作查询