# Indexes

PostgreSQL provides a variety of index types, all following the same fundamental principle: each index acts as a reference to a specific row within a table. This allows for direct access to the required data without having to scan the entire table. However, utilizing indexes introduces extra workload to database systems, thus it is crucial to implement them judiciously.

Indexes can be organized in various manners based on the values being indexed. The selection of index type is determined by the nature of the values. The most prevalent index type, and the default in Postgres, is the B-Tree. A B-Tree is a generalized version of a binary search tree, allowing nodes to have multiple children.

#### Create Index

```plsql
create table occupants (
  id bigint generated by default as identity primary key,
  age int,
  height int,
  weight int,
  name text,
  deceased boolean
);

```

It may be necessary to regularly request information from users pertaining to their age.

```plsql
select name from occupants where age = 32;
```

Postgres would need to search through each individual row in the table without an index in order to locate exact age matches. You can verify this by doing an explain on the query:

```plsql
explain select name from persons where age = 32;

//Output
Seq Scan on persons  (cost=0.00..22.75 rows=x width=y)
Filter: (age = 32)
```

To add a simple B-Tree index you can run:

```plsql
create index idx_occupants_age on occupants (age);
```

Building indexes on extensive collections of data may consume a substantial amount of time, and the create index command typically locks the table to prevent write operations during this process. Fortunately, Postgres offers the "create index concurrently" feature, which effectively prevents write operations from being blocked on the table. However, this process may entail a slightly longer duration for completion.

#### Partial Indexes

If you are consistently searching for a specific subset of rows, it could be advantageous to create a partial index. For instance, suppose we only need to filter by age when the deceased status is false. In this case, we can construct a partial index:

```plsql
create index idx_living_occupants_age on occupants (age)
where deceased is false;
```

### Ordering indexes <a href="#ordering-indexes" id="ordering-indexes"></a>

B-Tree indexes are typically organized in ascending order, but there may be instances where a different sorting arrangement is desirable. For example, in certain cases within our application, such as showcasing the top 10 eldest occupants, a descending order and placement of NULL values at the end may be preferred. In such situations, the following method can be employed:

```plsql
create index idx_occupants_age_desc on occupants (age desc nulls last);
```

### Reindexing <a href="#reindexing" id="reindexing"></a>

Over time, indexes in a database may lose their effectiveness and require regeneration. Postgres offers a reindex command for this purpose; however, considering that Postgres locks the index during this operation, it may be advisable to utilize the concurrent keyword to avoid interruptions.

```plsql
reindex index concurrently idx_occupants_age;
```

Another option is to rebuild all indexes associated with a specific table.

```plsql
reindex table concurrently occupants;
```

Please be aware that the reindex command can be executed within a transaction, while the reindex option with the 'concurrently' keyword is not compatible with transactions.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.appizap.ae/database/table-schema/indexes.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.