title: How to use gin indexes in postgresql
body: <p>I have never used GIN indexing before and I am trying to understand how it works.
  I have a static table with 4million rows, which I need to run SELECT queries with WHERE clauses.</p>
  
  <p>Right now my table has a B-Tree index on the column "name" which is used in the where clause.</p>
  
  <p>I have read that doing a full text search using a GIN index can greatly improve the speed. I have read the tutorial <a href="https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/" rel="nofollow noreferrer">here</a> and right now I am not sure if this is something I can use when I have only one table.</p>
  
  <p>Does full text search works in case of multiple tables that we want to join?</p>
  
  <p>I understand that the GIN Indexing works on a tsvector column. This is what I have done till now:</p>
  
  <p>I have altered my table and added one more column: tsv:</p>
  
  <pre><code>ALTER TABLE my_table ADD COLUMN tsv tsvector;
  </code></pre>
  
  <p>I createad a GIN index on my ts_vector column:</p>
  
  <pre><code>CREATE INDEX tsv_idx ON my_table USING gin(to_tsvector('english', name));
  </code></pre>
  
  <p>And I have updated the column:</p>
  
  <pre><code>UPDATE my_table SET tsv = to_tsvector(name)
  </code></pre>
  
  <p>Are the above correct? So now if I want to run the following query:</p>
  
  <pre><code>SELECT clm1, clmn2 FROM my_table WHERE name LIKE 'Ath%' ORDER BY 1
  </code></pre>
  
  <p>how can I do it using the GIN index?</p>
  
  <p>I tried this:</p>
  
  <pre><code>SELECT clm1, clmn2 FROM my_table WHERE tvs LIKE 'Ath%' ORDER BY 1
  </code></pre>
  
  <p>But I get an error:</p>
  
  <pre><code>ERROR:  operator does not exist: tsvector ~~ unknown
  </code></pre>
  
  <p>What am I missing here? What is the correct use of GIN? And can I apply full text search on a single table?</p>