# 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](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2F8ab76dd3c5d50d89e6e696655754066d9a5ac761.png?generation=1628771052536942\&alt=media)

## Creating a GIN Index

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

![image](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2Fdee0ac341ae61f4ba1d9829ed77008583808b0af.png?generation=1628771052204908\&alt=media)

## Get Size of Index

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

![image](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2Fda7ea012efa1307cc5f07937fc4a13111e9fc462.png?generation=1628771051984469\&alt=media)

## Using JSONB\_PATH\_OPS ( better )

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

![image](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2Fd52103a82c0ff0a229523d4678d173886b1bdb20.png?generation=1628771052475658\&alt=media)

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

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

![image](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2Fe8c148e1180acff0c857039a1b990fdd2762b379.png?generation=1628771052358320\&alt=media)

## 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](https://4266709838-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-MguJ2NLZYzXs1aLLRCO%2Fsync%2F3d2e0d1a237b88ab4ccd5e6d7efd6dcfec916708.png?generation=1628771052279350\&alt=media)