7. Create an index

7.1. Spherical index

pgSphere uses GiST to create spherical indices. An index speeds up the execution time of operators <@, @, &&, #, =, and !=. You can create an index with the following spherical data types:

• point (spoint)

• circle (scircle)

• line (sline)

• ellipse (sellipse)

• polygon (spoly)

• path (spath)

• coordinates range (sbox)

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

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.

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