Re: Complex filters -> Bad row estimates -> bad query plan

If those conditions that are throwing off the stats are expected to be
minimally impactful/filtering few rows, then you can use the one
tried-and-true optimizer hint (aside from materialized CTEs, stylized
indexes, etc) --- OFFSET 0 at the end of a sub-query.

SELECT * FROM ( [your existing query without the sub-selects that are
complicated and produce bad estimates] OFFSET 0 ) WHERE [your other
conditions that don't produce good estimates]

If there is correlation between field1 and field2, you might also look at
CREATE STATISTICS assuming you are on PG 10 or 11.

Before I do any of that, I would try LEFT JOIN for Table3 and Table4 then
use the where conditon "AND 2 = COALESCE( Table3.Status, Table4.Status"
and see if the optimizer likes that option better.