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

Question

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

postgresqlindexingdate-rangeb-tree-indexgist-index

7

我有一个简单的表格，其中有一个名为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)
)

该字段有一个索引（B树），规则为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则使用了。

需要注意的是，这个规则的实际用例在一个更复杂的查询中，不会导致重新检查条件和位图堆扫描。在应用程序复杂查询中，两种方法（有120万条记录）之间的差异是巨大的：查询1为415毫秒，查询2为84毫秒。

这是daterange的错误吗？我做错了什么吗？还是datarange <@的性能符合设计？

还有一个pgsql-bugs邮件列表的讨论。

- Shahar Hadas

如果你运行 analyze users;，然后执行查询#1，执行计划会发生什么？ - Mike Sherrill 'Cat Recall'

这是我看到这个问题时尝试的第一件事之一。它没有任何影响。 - Shahar Hadas

为什么要在“NOT user_birthday IS NULL”上建立索引，而不是日期本身？ - Mike Sherrill 'Cat Recall'

我正在忽略没有生日的记录，所以我不需要对它们进行索引。这样做的理由是为了保持索引较小。根据@ErwinBrandstetter的要求，添加了CREATE INDEX，尽管如上所述-其中没有什么特别的。 - Shahar Hadas

1

同意，如果您有范围类型并且需要检查上限/下限、排除/包含、NULL以构建语句，则可能会有点笨拙。如果Postgres能够为我们完成这项工作，那将是很好的。 - Erwin Brandstetter

显示剩余3条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

BETWEEN 包含上限和下限。你的条件

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

匹配

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

我看到你提到了btree索引。对于这种情况，请使用简单比较运算符。

详细了解哪种索引适用于哪些运算符。

范围类型运算符<@ 或 @> 与 GiST 索引一起使用。
例如：
在PostgreSQL中执行此营业时间查询。