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

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)