You forgot to mentionWorth mentioning that you installed the additional module pg_trgmpg_trgm, which provides the similarity() function.
First of all, whateverWhatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And indexIndex support is bound to operators in Postgres, not to functions.
To get the desired minimum similarity of 0.6, runset the GUC parameter:
SELECTSET set_limit(pg_trgm.similarity_threshold = 0.6);6; -- once per session
The(In Postgres 9.6 or older use the deprecated SELECT set_limit(0.6);)
The setting stays for the rest of your session unlessuntil reset to something else. Check with:
SELECTSHOW show_limit();pg_trgm.similarity_threshold;
This is a bit clumsy, but great for performance.(Used to be SELECT show_limit();)
Simple case
###Simple case
If you just wantedJust getting the best matches in column job_title for the given string 'sales executive' then this would would be a simpleplain case of "nearest neighbor" search and could be solved with a GiST index using the trigram operator class gist_trgm_ops (but not with a GIN index):
To also include an equality condition on worksite_city you would need the additional module btree_gistbtree_gist. Run (once per DB):
SELECT set_limit(0.6); -- once per session
SELECT *
FROM lca_test
WHERE job_title % 'sales executive'
AND worksite_city = 'los angeles'
ORDER BY (job_title <-> 'sales executive')
LIMIT 50;
SET pg_trgm.similarity_threshold = 0.6 -- once per session
SELECT *
FROM lca_test
WHERE job_title % 'sales executive'
AND worksite_city = 'los angeles'
ORDER BY (job_title <-> 'sales executive')
LIMIT 50;
<-><-> being the "distance" operator:
Postgres can also combine two separate indexes, a plain btree index on worksite_city, and a separate GiST index on job_title, but the multicolumn index should be fastest - if you combinewhen combining the two columns like this in queries regularlyyou did.
Your case
###Your case
HoweverHowever, your query sorts by salary, not by distance /or similarity, which changes the nature of the game completelyis something else entirely. Now we can use both GIN and GiST index, and GIN will be faster. (evenEven more so in Postgres 9.4 which has largely improvedlater version with major improvements to GIN indexes - upgrade hint!)
Similar story for the additional equality check on worksite_city: install the additional module btree_ginbtree_gin. Run (once per DB):
SELECT set_limit(0.6); -- once per session
SELECT *
FROM lca_test
WHERE job_title % 'sales executive'
AND worksite_city = 'los angeles'
ORDER BY salary
LIMIT 50 -- OFFSET 0
SET pg_trgm.similarity_threshold = 0.6; -- once per session
SELECT *
FROM lca_test
WHERE job_title % 'sales executive'
AND worksite_city = 'los angeles'
ORDER BY salary
LIMIT 50; -- OFFSET 0
Again, this should also workworks (less efficiently) with the simpler index you already have ("index_lcas_job_title_trigram"), possibly in combination with other indexes. The best solution depends on the complete picture.
###AsidesFurther reading:
Asides
You have a lot of indexes. Are you sure they are all in use and pay their maintenance cost?
You have some dubious data types:
employement_start_date | character varying
employement_end_date | character varying
Related answers:
