Comments (4)
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.
from pg_similarity.
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...
from pg_similarity.
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?
from pg_similarity.
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)
from pg_similarity.
Related Issues (20)
- Native MSVC build support HOT 4
- How to install on version 9.5 HOT 4
- Needleman-Wunsch crashes the Postgres server HOT 14
- Installation on Postgresql 9.5 - pg_similarity.control: No such file or directory HOT 1
- could not load library HOT 18
- Cannot install HOT 2
- Could not open extension control file “/usr/share/postgresql/10/extension/pg_similarity.control”: No such file or directory HOT 2
- cosine and jaro on perfect matches HOT 1
- Regression tests fail with PG12 HOT 6
- Comparing timestamps
- Empty strings with ~*~ crashes server
- pg_config editting HOT 1
- Wrong loop variable in levenshtein.c
- Unable to install the extension in rhel7 and postgres version is Postgres13.3 HOT 1
- Querying a table with jaccard similarity with 1.6 million records take 12 seconds HOT 2
- set PGS_MAX_STR_LEN at runtime HOT 3
- Possible Bug in Dice (and Jaccard) Function
- Trying to get pg_similarity extension on windows
- ERROR: argument exceeds the maximum length of 1024 bytes HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from pg_similarity.