7. Create an index

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:

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

Example 67. 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;
          

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

7.2. smoc index

pgSphere uses GIN to create smoc indices. An index speeds up the execution time of operators <@, @>, &&, =, and <>.

The index works by casting all contained smocs to a fixed level, and for each pixel at that level, storing which smocs overlap with that pixel. This is especially beneficial for "overlaps" queries using the && operator. Two levels of granularity are provided: the default opclass smoc_gin_ops works on level 5 with a resolution of 12288 pixels, while the opclass smoc_gin_ops_fine works on level 8 with 786432 pixels. The downside of that approach is that storing large smocs like "all sky" (0/0-11) produces a large number of index entries.

Example 68. Index of smoc coverage objects


CREATE TABLE ivoa (
  coverage smoc NOT NULL
);
-- Put in data now
CREATE INDEX ON ivoa USING GIN (coverage);
-- Alternative index with more detail
CREATE INDEX ivoa_fine_idx ON ivoa USING GIN (coverage smoc_gin_ops_fine);