You forgot to mention that you installed the additional module pg_trgm, which provides the similarity() function.
Similarity operator %
First of all, whatever else you do, use the similarity operator % instead of the expression (similarity(job_title, 'sales executive') > 0.6). Much cheaper. And index support is bound to operators in Postgres, not to functions.
To get the desired minimum similarity of 0.6, run:
SELECT set_limit(0.6);
The setting stays for the rest of your session unless reset to something else. Check with:
SELECT show_limit();
This is a bit clumsy, but great for performance.
###Simple case
If you just wanted the best matches in column job_title for the string 'sales executive' then this would be a simple 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):
CREATE INDEX trgm_idx ON lcas USING gist (job_title gist_trgm_ops);
To also include an equality condition on worksite_city you would need the additional module btree_gist. Run (once per DB):
CREATE EXTENSION btree_gist;
Then:
CREATE INDEX lcas_trgm_gist_idx ON lcas USING gist (worksite_city, job_title gist_trgm_ops);
Query:
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;
<-> being the "distance" operator:
one minus the
similarity()value.
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 combine the two columns like this in queries regularly.
###Your case
However, your query sorts by salary, not by distance / similarity, which changes the nature of the game completely. Now we can use both GIN and GiST index, and GIN will be faster (even more so in Postgres 9.4 which has largely improved GIN indexes - hint!)
Similar story for the additional equality check on worksite_city: install the additional module btree_gin. Run (once per DB):
CREATE EXTENSION btree_gin;
Then:
CREATE INDEX lcas_trgm_gin_idx ON lcas USING gin (worksite_city, job_title gin_trgm_ops);
Query:
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
Again, this should also work (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.
###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
Seems like those should be date. Etc.
Related answers:
