postgresql - postgres 不使用 btree_gist 索引

Question

我有一个带有主键和 btree_gist 索引的巨大表。当我查询 btree_gist 索引中的列时，我希望使用该索引并且查询执行得相当快。但是，优化器始终对主键和过滤器执行索引扫描。

例子:

create table test1 (
    id1 bigint not null,
    id2 bigint not null,
    validtime tstzrange not null,
    data float);
alter table test1 add constraint pk_test1 primary key (id1, id2, validtime);
alter table test1 add constraint ex_test1_validtime exclude using gist (id1 with =, id2 with =, validtime with &&);

该表包含大约 12 亿行，我想知道的查询只返回了几百行，但需要很长时间:

select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
(about 3s)

查询计划:

explain select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Index Scan using pk_test1 on test1  (cost=0.70..24.68 rows=1 width=46)
   Index Cond: ((id1 = 1) AND (id2 = 1))
   Filter: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

性能不佳的原因显然是在时间条件中读取和过滤了数万行。

我想知道为什么 postgres 不使用 btree_gist。

---

我有另一个稍微不同的表，其中使用了 btree_gist，但与我预期的方式非常不同。该表有大约 1.6 亿行。

create table test2 (
    id1 bigint not null,
    validtime tstzrange not null);                                                                                                                                                                                                                                                                                                                                              
alter table test2 add constraint pk_test2 primary key (id1, validtime);
alter table test2 add constraint ex_test2_validtime exclude using gist (id1 with =, validtime with &&);

这里的执行计划是这样的:

select * from test2 where id1=1 and validtime && '[2020-01-01,2020-02-01)';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test2  (cost=1933.19..1937.20 rows=1 width=62)
   Recheck Cond: ((id1 = 1) AND (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange))
   ->  BitmapAnd  (cost=1933.19..1933.19 rows=1 width=0)
         ->  Bitmap Index Scan on pk_test2  (cost=0.00..574.20 rows=11417 width=0)
               Index Cond: (id1 = 1)
         ->  Bitmap Index Scan on ex_test2_validtime  (cost=0.00..1358.74 rows=17019 width=0)
               Index Cond: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

为什么两次位图索引扫描，使用 btree_gist 索引一次扫描就不能全部完成？

Answer 1

终于找到了:

由于查询和索引之间的类型不匹配，因此未使用索引。实际上到处都提到了它，但我只是阅读了一下。

1 显然不是 bigint!有趣的是，使用 btree 主键而不是 btree_gist 会自动进行转换。

无论如何，对于这个查询，一切都按预期工作:

select * from test1
where id1=1::bigint and id2=1::bigint
and validtime && '[2020-01-01,2020-02-01)';

学习这个花了我几个小时，我永远不会忘记它!