Обсуждение: [GENERAL] index on search - pg 9.2

SELECT j.id, ff.gtime
FROM public.status AS s
JOIN public.job AS j ON j.status_label_id = s.id AND j.clientid = 3369
JOIN public.log AS ff ON ff.jobid = j.id
AND ff.clientid = 3369
AND (ff.description LIKE '%change%')
ORDER BY gtime DESC
LIMIT 100

Explain analyze: https://explain.depesz.com/s/1OLW

I'm using PG 9.2 and, read about gin indexes.

I've created the index to test, but the query is not using it.

create index on log gin (description gin_trgm_ops)

Can you guys help to improve that part please?

that index won't be of any use if ff.clientid=3369 selects fewer records than (ff.description like '%change%') would on its own.    and you don't even have a WHERE clause, you piled all your conditions on the JOIN ON clauses.   the only things there that are actually join clauses are the things that join two tables, such as ff.jobid=j.id

functionally, your query is equivalent to...

-- 
john r pierce, recycling bits in santa cruz