PolarDB:GiST index

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 output:

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

Create a GiST index on the pos column.

CREATE INDEX idx_t_gist_1 on t_gist USING gist(pos);

Run a containment query. The following query finds all points within a circle centered at (100,100) with radius 10.

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

Sample output:

                                                            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)

Run a nearest-neighbor query. The following query finds the 10 points closest to (100,100) within a circle of radius 1, sorted by distance.

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

Sample output:

                                                                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)

Install the btree_gist extension and create test data.

-- Install 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));

Create a GiST index on the id column.

CREATE INDEX idx_t_btree_2 ON t_btree USING gist(id);

Run a nearest-neighbor query to find the row with the id closest to 100.

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

Sample output:

                                                                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)