On Mon, 2021-03-22 at 08:10 -0500, Chris Stephens wrote: > 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.
