GiST索引

几何类型：支持位置搜索（包含、相交、在上下左右等），按距离排序。

1. 准备测试数据

   CREATE TABLE t_gist(id int, pos point);
   INSERT INTO t_gist SELECT generate_series(1,100000), point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2));
   SELECT * FROM t_gist LIMIT 3;

   返回结果如下：

    id |       pos       
   ----+-----------------
     1 | (260.5,370.1)
     2 | (258.96,540.17)
     3 | (704.15,511.91)
   (3 rows)

2. 创建几何索引。

   CREATE INDEX idx_t_gist_1 on t_gist USING gist(pos);

3. 执行计划查询。

   • EXPLAIN (analyze, verbose, timing, costs, buffers) SELECT * FROM t_gist WHERE circle '((100,100) 10)' @> pos;

     返回结果如下：

                                                            QUERY PLAN                                                       
     ------------------------------------------------------------------------------------------------------------------------
      Bitmap Heap Scan on public.t_gist  (cost=2.15..103.45 rows=100 width=20) (actual time=1.302..1.334 rows=31 loops=1)
        Output: id, pos
        Recheck Cond: ('<(100,100),10>'::circle @> t_gist.pos)
        Heap Blocks: exact=31
        Buffers: shared hit=31 (main=31 vm=0 fsm=0) read=9 (main=9 vm=0 fsm=0)
        I/O Timings: shared/local read=1.190
        Read Timings: total=1.222 ms buffer alloc=0.017 read io=1.192 page replay=0.000
        ->  Bitmap Index Scan on idx_t_gist_1  (cost=0.00..2.13 rows=100 width=0) (actual time=1.293..1.293 rows=31 loops=1)
              Index Cond: (t_gist.pos <@ '<(100,100),10>'::circle)
              Buffers: shared read=9 (main=9 vm=0 fsm=0)
              I/O Timings: shared/local read=1.190
              Read Timings: total=1.222 ms buffer alloc=0.017 read io=1.192 page replay=0.000
      Query Identifier: -1217620156139536529
      Planning:
        Buffers: shared hit=38 (main=38 vm=0 fsm=0) dirtied=3 (main=0 vm=0 fsm=0)
      Planning Time: 0.144 ms
      Execution Time: 1.370 ms
     (17 rows)

   • EXPLAIN (analyze,verbose, timing,costs, buffers) SELECT * FROM t_gist WHERE circle '((100,100) 1)' @> pos ORDER BY pos <-> '(100,100)' LIMIT 10;

     返回结果如下：

                                                                   QUERY PLAN                                                               
     ---------------------------------------------------------------------------------------------------------------------------------------
      Limit  (cost=0.28..10.96 rows=10 width=28) (actual time=0.050..0.050 rows=0 loops=1)
        Output: id, pos, ((pos <-> '(100,100)'::point))
        Buffers: shared hit=7 (main=7 vm=0 fsm=0)
        ->  Index Scan using idx_t_gist_1 on public.t_gist  (cost=0.28..107.03 rows=100 width=28) (actual time=0.049..0.049 rows=0 loops=1)
              Output: id, pos, (pos <-> '(100,100)'::point)
              Index Cond: (t_gist.pos <@ '<(100,100),1>'::circle)
              Order By: (t_gist.pos <-> '(100,100)'::point)
              Buffers: shared hit=7 (main=7 vm=0 fsm=0)
      Query Identifier: -131225390984756690
      Planning:
        Buffers: shared hit=16 (main=16 vm=0 fsm=0)
      Planning Time: 0.071 ms
      Execution Time: 0.087 ms
     (13 rows)

范围类型：支持位置搜索（包含、相交、在左右等）。

IP类型：支持位置搜索（包含、相交、在左右等）。

空间类型（PostGIS）：支持位置搜索（包含、相交、在上下左右等），按距离排序。

1. 准备测试数据。

   --- 安装插件
   CREATE EXTENSION btree_gist;
   
   --- 准备测试数据
   CREATE TABLE t_btree(id int, info point);
   INSERT INTO t_btree SELECT generate_series(1,10000), point(round((random()*1000)::numeric, 2), round((random()*1000)::numeric, 2));

2. 创建相关索引。

   CREATE INDEX idx_t_btree_2 ON t_btree USING gist(id);

3. 执行计划查询。

   EXPLAIN (analyze,verbose,timing,costs,buffers) SELECT * FROM t_btree ORDER BY id <-> 100 LIMIT 1;

   返回结果如下：

                                                                   QUERY PLAN                                                                 
   -------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.15..0.18 rows=1 width=24) (actual time=0.041..0.041 rows=1 loops=1)
      Output: id, info, ((id <-> 100))
      Buffers: shared hit=3 (main=3 vm=0 fsm=0)
      ->  Index Scan using idx_t_btree_2 on public.t_btree  (cost=0.15..342.05 rows=10000 width=24) (actual time=0.040..0.040 rows=1 loops=1)
            Output: id, info, (id <-> 100)
            Order By: (t_btree.id <-> 100)
            Buffers: shared hit=3 (main=3 vm=0 fsm=0)
    Query Identifier: 8747019630547498538
    Planning:
      Buffers: shared hit=32 (main=32 vm=0 fsm=0) dirtied=3 (main=0 vm=0 fsm=0)
    Planning Time: 0.105 ms
    Execution Time: 0.064 ms
   (12 rows)

标量类型：支持按距离排序。

<<

&<

&>

>>

<<|

&<|

<@

~=

&&

|>>

@>

|&>