7.1. Spherical index
Prev | Up | Chapter 7. Indexes | Home | Next

## 7.1. Spherical index #

pgSphere uses `GiST` and Block Range INdexing (`BRIN`) algorithms to create spherical indices. `GiST` indexes utilize an R-tree implementation for spherical objects, while `BRIN` indexes are based on the "summarization" of data blocks (`pages`) on physical storage in order to organize data searches on ranges of summarized data that can be easily skipped on the base of search filters (see PostgreSQL documentation for further details on BRIN indexes). As a consequence, BRIN indexes are very small indexes (up to 1000 times smaller than GiST ones), generally with lower performance compared with a GiST one, but up to 100 times faster than a full sequential scan of a table performed without any index. So BRIN indexes are particularly suitable in a big data context. An index speeds up the execution time of searches based on operators `<@`, `@`, `&&`, `#`, `=`, and `!=`.

You can create a GiST index with the following spherical data types:

- point (`spoint`)

- circle (`scircle`)

- line (`sline`)

- ellipse (`sellipse`)

- polygon (`spoly`)

- path (`spath`)

- coordinates range (`sbox`)

A GiST index can be also used for quickly finding the points closest to the given one when ordering by an expression with the `<->` operator, as shown in an example below.

BRIN indexing supports just spherical points (`spoint`) and spherical coordinates range (`sbox`) at the moment.

Example 7.1. Simple index of spherical points

    CREATE TABLE test (
      pos spoint NOT NULL
    );
    -- Put in data now
    CREATE INDEX test_pos_idx ON test USING GIST (pos);
    VACUUM ANALYZE test;

To find the points closest to a given spherical position, use the `<->` operator:

    SELECT * FROM test ORDER BY pos <-> spoint (0.2, 0.3) LIMIT 10

BRIN index can be created through the following syntax:

    CREATE INDEX test_pos_idx USING BRIN ON test (pos);

By default, BRIN indexes summarize blocks of 128 pages. The smaller the number of pages specified, the higher the granularity in searches, and the gap in performance between GiST indexes and BRIN indexes will be decreased. Note that the size of the BRIN indexes increases as well. Different summarizations can be specified with the following command:

    CREATE INDEX test_pos_idx USING BRIN ON test (pos) WITH (pages_per_range = 16);

Prev | Up | Next
Chapter 7. Indexes | Home | 7.2. `smoc` index