Skip to main content

Indices

An index is a data structure the database maintains next to a table so that lookups, joins and sorts on the indexed columns do not have to scan every row. On a GC2 (Geo Compute Cloud) table you typically add a btree index on columns used in where clauses and joins, a gist index on a geometry column so spatial queries and map rendering through the OGC services stay fast, and a gin index for JSON or array columns. The Index API adds, inspects and drops indices; they can also be declared inline when a table is created.

Indices in a database use a similar approach as in most non-fiction books: terms and concepts that are frequently looked up by readers are collected in an alphabetic index at the end of the book. The interested reader can scan the index relatively quickly and flip to the appropriate page(s), rather than having to read the entire book to find the material of interest.

Add index​

An index can be of either type btree, brin, gin, gist or hash.

Create an index
POST https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees/indices HTTP/1.1
Content-Type: application/json
Authorization: Bearer abc123

{
    "name": "inductees-id-btree",
    "columns": ["id"],
    "method": "btree"
}

Get index​

You can either get a single index or a collection of indices.

All indices for a table
GET https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees/indices HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123
Specific index by name
GET https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees/indices/my-btree-index-on-id HTTP/1.1
Accept: application/json; charset=utf-8
Authorization: Bearer abc123

An index object looks like this.

{
  "name": "btree-on-id",
  "columns": ["id"],
  "method": "btree"
}

Delete index​

Delete index by name
DELETE https://api.gc2.io/api/v4/schemas/rockhall/tables/inductees/indices/inductees-id-btree HTTP/1.1