Understanding Postgresql Indexes For Beginners- Part -4 (Gin)

In the last blog we learned about BRIN index parameters and when to choose it. In this we will learn basic of Gin index.

Gin index is the that can speed up your full text search, lets understand how.

Gin Index

Gin Index – (Generalized Inverted Index) – An inverted index is an index data structure storing a mapping from content, such as words or numbers, to its locations in a document or a set of documents. In simple words, it is a hashmap like data structure that directs you from a word to a document or a web page. If you want more detail reading go to.

Now in case of Postgresql when we create any Gin index on a text column or the type of column is tsvector (A tsvector value is a sorted list of distinct lexemes, which are words that have been normalized to merge different variants of the same word )

Gin does is take all the distinct lexemes and break them into trigrams and map to the rows it points to and it will point to multiple rows if multiple rows contains that lexemes.

Now in this way when we search the any three character in the column and gin index will return the result in fast manner.

Let’s see this in action:

First create a table:

create table student(name  text , address tsvector);

Now let’s add some data:

INSERT INTO student ( name , address) SELECT md5(random()::text), md5(random()::text)::tsvector FROM generate_series(1,800000) as g;

Now lets check for search in name (text) and address (ts_vector fields)

 explain analyze select * from student where name ilike '%puneet%';
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..98897.03 rows=1887 width=64) (actual time=316.414..319.296 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on student  (cost=0.00..97708.33 rows=786 width=64) (actual time=307.882..307.883 rows=0 loops=3)
         Filter: (name ~~* '%puneet%'::text)
         Rows Removed by Filter: 266667
 Planning time: 0.089 ms
 Execution time: 319.353 ms



explain analyze select * from student where address @@ to_tsquery('gurgaon'); 
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..154940.76 rows=3956 width=74) (actual time=515.955..518.074 rows=0 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on student  (cost=0.00..153545.16 rows=1648 width=74) (actual time=507.235..507.235 rows=0 loops=3)
         Filter: (address @@ to_tsquery('gurgaon'::text))
         Rows Removed by Filter: 266667
 Planning time: 0.125 ms
 Execution time: 518.102 ms
(8 rows)

Now you see its taking 391 ms and 518 ms

Let’s create indexes :

create index a_g_idx on student using gin(address);

CREATE EXTENSION IF NOT EXISTS pg_trgm;

create index name_g_idx on student using gin(name gin_trgm_ops);

Now let’s see the time:

explain analyze select * from student where address @@ to_tsquery('gurgaon'); 
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on student  (cost=55.25..13838.72 rows=4000 width=74) (actual time=0.067..0.068 rows=0 loops=1)
   Recheck Cond: (address @@ to_tsquery('gurgaon'::text))
   ->  Bitmap Index Scan on a_g_idx  (cost=0.00..54.25 rows=4000 width=0) (actual time=0.063..0.064 rows=0 loops=1)
         Index Cond: (address @@ to_tsquery('gurgaon'::text))
 Planning time: 0.472 ms
 Execution time: 0.140 ms


explain analyze select * from student where name ilike '%puneet%';
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on student  (cost=60.62..373.33 rows=80 width=74) (actual time=0.032..0.033 rows=0 loops=1)
   Recheck Cond: (name ~~* '%puneet%'::text)
   ->  Bitmap Index Scan on name_g_idx  (cost=0.00..60.60 rows=80 width=0) (actual time=0.029..0.030 rows=0 loops=1)
         Index Cond: (name ~~* '%puneet%'::text)
 Planning time: 6.547 ms
 Execution time: 0.083 ms

you see a huge difference in timings : from 319 ms to 0.14 and 518 to 0.083

Now we have seen the power of Gin Index in Postgresql and what text search in can provide.

Surely there are various parameters to configure and various other use cases of text search that can be done with gin.

Here our purpose was to understand its basics, in future we will work on details also.

In next blog we will work on Partial Indexs. Stay tuned and subscribe our blog for more such blogs.

One thought on “Understanding Postgresql Indexes For Beginners- Part -4 (Gin)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s