Example of how to add Indexes

[gosom]

Can you please add an example of how to add indexes and what the use_index means ? Is adding the indexes and the operators possible on a JSONB column?

[eulerto]

pg_similarity supports GIN indexes. Indexes are not supported by all functions (check OPERATOR CLASS in pg_similarity--1.0.sql for the supported operators).

pg_similarity is for text data types. However, cast to text should do the job.

[alexitheodore]

I understand which functions/operators can be indexed, but I am still very unclear on how to index them. All the functions/operators require two inputs, so something like:

CREATE INDEX ON table_name USING GIN( ~** column_name );

would not work of course...

[alexitheodore]

Ok, I - miraculously - figured it out.

CREATE INDEX ON {table_name} USING GIN({column_name} gin_similarity_ops);

@eulerto May I request that this simple, but essential hint be put into documentation?

Also, confirm that I have it right?

[eulerto]

Your assumption is correct. Example:

euler=# create table dictptbr (p text);
CREATE TABLE
euler=# copy dictptbr (p) from '/usr/share/dict/brazilian';
COPY 275502
euler=# create index on dictptbr using gin(p gin_similarity_ops);
CREATE INDEX
euler=# explain (analyze, buffers) select * from dictptbr where p ~## 'Abadia';
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                        QUERY PLAN                                                        │
╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Bitmap Heap Scan on dictptbr  (cost=18.14..716.98 rows=276 width=11) (actual time=0.101..0.103 rows=1 loops=1)           │
│   Recheck Cond: (p ~## 'Abadia'::text)                                                                                   │
│   Heap Blocks: exact=1                                                                                                   │
│   Buffers: shared hit=4 read=1                                                                                           │
│   ->  Bitmap Index Scan on dictptbr_p_idx  (cost=0.00..18.07 rows=276 width=0) (actual time=0.056..0.056 rows=1 loops=1) │
│         Index Cond: (p ~## 'Abadia'::text)                                                                               │
│         Buffers: shared hit=4                                                                                            │
│ Planning Time: 0.375 ms                                                                                                  │
│ Execution Time: 0.191 ms                                                                                                 │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)