we are but i was hoping to get a better understanding of where the optimizer is going wrong and what i can do about it.
chris On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.a...@cybertec.at> wrote: > 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. > > Great! Then use one of the rewritten queries. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
