Example of how to add Indexes
gosom opened this issue · 4 comments
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?
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.
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...
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?
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)