pgSphere 1.1.5
Prev |  | Next

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

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

## 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 56. 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);

Prev | Home | Next
Functions |  | Usage examples