HI Tom/Ninad
My bad I didn't explain my use case properly.
The use case is to find the best string similarity for a given address against the list of addresses in the table.
Initially I tried a similarity function provided by the pg_trgm extension.
But the similarity scores were not satisfactory.
Later I explored the pg_similarity extension which had an exhaustive list of functions which supported indexes primarily on GIN.
After trying multiple functions like levenshtein, cosine, qgram, jaro, jaro winkler and jaccard similarity functions
jaccard similarity functions were providing better accurate results.
Hence we decided to use that for our analysis.
Now with a small amount of data the query response time is better. But with such 1.6 million it is taking a long time.
Even the documentation owner of pg_similarity provided an example on how to create an index using GIN. We followed exactly the same process.
But still when the data is huge we don't know why it is taking time to scan through the records.
Thanks
C.R.Bala
Michael Lewis <mlewis@entrata.com> writes:
> This is showing many false positives from the index scan that get removed
> when the actual values are examined. With such a long search parameter,
> that does not seem surprising. I would expect a search on "raj nagar
> ghaziabad 201017" or something like that to yield far fewer results from
> the index scan. I don't know GIN indexes super well, but I would guess that
> including words that are very common will yield false positives that get
> filtered out later.
Yeah, the huge "Rows Removed" number shows that this index is very
poorly adapted to the query. I don't think the problem is with GIN
per se, but with a poor choice of how to use it. The given example
looks like what the OP really wants to do is full text search.
If so, a GIN index should be fine as long as you put tsvector/tsquery
filtering in front of it. If that's not a good characterization of
the goal, it'd help to tell us what the goal is. (Just saying "I
want to use jaccard similarity" sounds a lot like a man whose only
tool is a hammer, therefore his problem must be a nail, despite
evidence to the contrary.)
regards, tom lane
