Improve search performance by eliminating redundant joins

[punktilious]

Is your feature request related to a problem? Please describe. Each parameter table contains logical_resource_id which is a FK reference to the (type)_logical_resources table. This is reflected in search query joins. Because we need to filter out deleted resources, we also need to join against the (type)_resources table and include the predicate `is_deleted = 'N'.

However, each (type)_resources table also includes the logical_resource_id value, and since search parameters are only stored for the current resource version, we can join the parameter table directly to the (type)_resources table and eliminate the join to the logical_resources table unless we need the logical_id - which is usually the last step.

In addition, per #1705 and #1714, any joins between the (type)_logical_resources table and (type)_resources can be defined as lr.current_resource_id = r.resource_id. This gives a better plan than when the join condition also includes and lr.logical_resource_id = r.logical_resource_id. This is likely due to cardinality estimation not knowing that the two predicates are strongly correlated and thus underestimating the row count.

Describe the solution you'd like Remove joins to (type)_logical_resources unless the logical_id is required. When the join to (type)_logical_resources is required, only join using the current_resource_id.

Describe alternatives you've considered Do nothing and accept slower performance.

[punktilious]

Connected to #1385. This is a simpler change because it doesn't require any schema migration. But the two table join would still be required anytime you need the logical_id (whenever we do chaining). Adding the deleted flag to the logical_resources table as described by #1385 is ultimately a better choice because it means we only need to join to the resources table at the last moment to grab the payload.

[lmsurpre]

@punktilious do we still need this one?

[punktilious]

Yes, there are still some optimizations we can apply related to the joins here, so I'll hold onto this one.