Eeek! Is this one of those constructed queries? It would sure be nice if folks treated queries like function calls and didn't paste them together.
You should look into creating a number of views first. Views are compiled so you skip that cost, and you can easily test subqueries.
I can see that you are ordering by stuff which can't have an index, like lower(p.instance_name). This is never going to be fast.
But, the time something takes doesn't necessarily mean the query needs improvement, if it doesn't run very often, who cares?
