SQL performance issue (postgresql chooses a bad plan when a better one is available) - Mailing list pgsql-performance

The following SQL takes ~25 seconds to run. I'm relatively new to postgres but the execution plan (https://explain.depesz.com/s/N4oR) looks like it's materializing the entire EXISTS subquery for each row returned by the rest of the query before probing for plate_384_id existence. postgres is choosing sequential scans on sample_plate_384 and test_result when suitable, efficient indexes exist. a re-written query produces a much better plan (https://explain.depesz.com/s/zXJ6). Executing the EXISTS portion of the query with an explicit PLATE_384_ID yields the execution plan we want as well (https://explain.depesz.com/s/3QAK). unnesting the EXISTS and adding a DISTINCT on the result also yields a better plan.

I've tried tried the following:

disable parallel

set join_collapse_limit=1 and played with order of EXISTS/NOT EXISTS

changed work_mem and enable_material to see if that had any effect

VACUUM FULL'd TEST_RESULT and SAMPLE_PLATE_384 

created a stats object on (sample_id, sample_plate_384_id) for both TEST_RESULT and SAMPLE_PLATE_384 to see if that would help (they increment fairly consistently with each other)

I'm out of ideas on how to convince postgres to choose a better plan. any and all help/suggestions/explanations would be greatly appreciated. the rewritten SQL performs sufficiently well but i'd like to understand why postgres is doing this and what to do about it so i can't tackle the next SQL performance issue with a little more knowledge.