index on search - pg 9.2

On 3/14/2017 7:18 PM, Patrick B wrote:

SELECT j.id <http://j.id&gt;, ff.gtime
FROM public.status AS s
JOIN public.job AS j ON j.status_label_id = s.id <http://s.id&gt; AND
j.clientid = 3369
JOIN public.log AS ff ON ff.jobid = j.id <http://j.id&gt;
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...

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

--
john r pierce, recycling bits in santa cruz