Optimize this query: SQL
Here is some recommentations:

Make sure all the columns used in the joins are indexed properly.

if possible, replace subqueries with indexed temporary tables.

Move when possible the filtering clause from the join statement to the where statement (e.g. Where  pf.pathLength BETWEEN 2 AND 5)

Try to move the joins order to start filtering with the table which reduce the more efficiently the returned dataset.