【DB吐槽大会】第50期 - PG GiST距离排序操作符和过滤无法同时使用索引

背景

1、产品的问题点

• PG GiST距离排序操作符和过滤无法同时使用索引

2、问题点背后涉及的技术原理

• PG GiST索引支持空间距离排序, 例如按距离某个经纬度点的距离排序, 返回表里面的经纬度点.
• PG 支持距离操作符, 支持排序功能, 同时支持返回距离值.

• • 但是距离过滤不能使用索引

例子:

• 《PostgreSQL GiST Order by 距离 + 距离范围判定 + limit 骤变优化与背景原因》

create extension btree_gist;  
postgres=# \do  
                                             List of operators  
 Schema | Name |        Left arg type        |       Right arg type        |   Result type    | Description   
--------+------+-----------------------------+-----------------------------+------------------+-------------  
 public | <->  | bigint                      | bigint                      | bigint           |   
 public | <->  | date                        | date                        | integer          |   
 public | <->  | double precision            | double precision            | double precision |   
 public | <->  | integer                     | integer                     | integer          |   
 public | <->  | interval                    | interval                    | interval         |   
 public | <->  | money                       | money                       | money            |   
 public | <->  | oid                         | oid                         | oid              |   
 public | <->  | real                        | real                        | real             |   
 public | <->  | smallint                    | smallint                    | smallint         |   
 public | <->  | time without time zone      | time without time zone      | interval         |   
 public | <->  | timestamp with time zone    | timestamp with time zone    | interval         |   
 public | <->  | timestamp without time zone | timestamp without time zone | interval         |   
  
create table t_age(id int, age int);    
insert into t_age select generate_series(1,10000000), random()*120;    
create index idx_t_age_1 on t_age using gist (age);    
    
select * from t_age     
where     
(age <-> 25) < 1     
order by age <-> 25     
limit 100000;    
  
  
 Limit  (cost=0.42..10245.61 rows=100000 width=12) (actual time=0.161..8126.988 rows=83248 loops=1)  
   Output: id, age, ((age <-> 25))  
   Buffers: shared hit=9523157  
   ->  Index Scan using idx_t_age_1 on public.t_age  (cost=0.42..341506.11 rows=3333326 width=12) (actual time=0.160..8115.150 rows=83248 loops=1)  
         Output: id, age, (age <-> 25)  
         Order By: (t_age.age <-> 25)  
         Filter: ((t_age.age <-> 25) < 1)  
         Rows Removed by Filter: 9916752  
         Buffers: shared hit=9523157  
 Planning Time: 0.077 ms  
 Execution Time: 8133.808 ms  
(11 rows)  
  
postgres=# set enable_seqscan=off;  
SET  
postgres=# explain select * from t_age where (age <-> 25) <1 limit 100000;  
                                     QUERY PLAN                                        
-------------------------------------------------------------------------------------  
 Limit  (cost=10000000000.00..10000005827.44 rows=100000 width=8)  
   ->  Seq Scan on t_age  (cost=10000000000.00..10000194247.66 rows=3333326 width=8)  
         Filter: ((age <-> 25) < 1)  
(3 rows)        

3、这个问题将影响哪些行业以及业务场景

• 影响最大的时基于地理位置的互联网业务, 例如社交、O2O、出行等

4、会导致什么问题?

• 当需要搜索附近的N个点, 并且距离M以内的双重需求时, 不能同时使用1个索引来满足. (要么只能用于排序, 要么只能用于where过滤)
• 需要额外的filter计算, 如果满足条件(距离M以内)的记录数不足N条, 则导致扫描整个索引, 性能急剧下降.

5、业务上应该如何避免这个坑

• 可以使用function来解决这个问题, 每次filter判定是否已越界.

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• 需要自定义函数, 开发成本增加.

7、数据库未来产品迭代如何修复这个坑

• 希望能直接在内核层面支持, 同一个index既能支持按距离过滤又能支持按距离排序输出.