> For the complete documentation index, see [llms.txt](https://dev117uday.gitbook.io/databases/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://dev117uday.gitbook.io/databases/sql/indexing/gin-index.md).

# GIN Index

* GIN index stands for Generalised Invert Index.
* Speeds up full text searches
* A GIN index stores a set of (key, posting list) pairs, where a posting list is a set of row IDs in which the key occurs. The same row ID can appear in multiple posting lists, since an item can contain more than one key.
* Each key value is stored only once, so a GIN index is very compact for cases where the same key appears many times.

## Query

```sql
select * from contacts_docs
where body @> '{"first_name":"John"}';


explain select * from contacts_docs
where body @> '{"first_name":"John"}';
```

![image](/files/-MguJT69mLI_3DuOKVjx)

## Creating a GIN Index

```sql
create index idx_gin_contacts_docs_body on contacts_docs USING GIN(body);
```

![image](/files/-MguJT6A8CCHraf8gXfH)

## Get Size of Index

```sql
select pg_size_pretty((pg_relation_size('idx_gin_contacts_docs_body'::regclass))) 
    as index_name;
```

![image](/files/-MguJT6Bw1kvZ9DdkpF_)

## Using JSONB\_PATH\_OPS ( better )

```sql
create index idx_gin_contacts_docs_body_cool
    on contacts_docs USING GIN(body jsonb_path_ops);
```

![image](/files/-MguJT6Cna-6bu3lzs9L)

### Size with jsonb\_*path\_ops*

```sql
select pg_size_pretty((pg_relation_size('idx_gin_contacts_docs_body_cool'::regclass))) 
    as index_name;
```

![image](/files/-MguJT6D5Qgb6d4OMePI)

## On Specific column for smaller size ( not working )

```sql
select pg_size_pretty((pg_relation_size('idx_gin_contacts_docs_body_fname'::regclass))) 
    as index_name;
```

![image](/files/-MguJT6EmpD6oGCc3H9k)