PolarDB:GiST index

Geometric type: supports location searches based on relative position operators such as inclusion, intersect, and located above, below, on the left, and on the right. The locations are sorted by distance.

1. Create test data.

   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;

   Sample result:

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

2. Create an index.

   CREATE INDEX idx_t_gist_1 on t_gist USING gist(pos);

3. Execute the query plan.

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

     Sample result:

                                                            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;

     Sample result:

                                                                   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)

Range type: supports location searches based on relative position operators such as inclusion, intersect, and located on the left and on the right.

IP type: supports location searches based on relative position operators such as inclusion, intersect, and located on the left and on the right.

Spatial type (PostGIS): supports location searches based on relative position operators such as inclusion, intersect, and located above, below, on the left, and on the right. The locations are sorted by distance.

1. Create test data.

   --- Create the extension.
   CREATE EXTENSION btree_gist;
   
   --- Create test data.
   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 an index.

   CREATE INDEX idx_t_btree_2 ON t_btree USING gist(id);

3. Execute the query plan.

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

   Sample result:

                                                                   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)

Scalar type: allows locations to be sorted by distance.

<<

&<

&>

>>

<<|

&<|

<@

~=

&&

|>>

@>

|&>