"set enable_material=false;" produces an efficient plan. good to know there are *some* knobs to turn when the optimizer comes up with a bad plan. would be awesome if you could lock that plan into place w/out altering the variable.
thanks for the help Hannu! On Mon, Mar 22, 2021 at 4:39 PM Hannu Krosing <han...@google.com> wrote: > you can play around various `enable_*` flags to see if disabling any > of these will *maybe* yield the plan you were expecting, and then > check the costs in EXPLAIN to see if the optimiser also thinks this > plan is cheaper. > > > On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens <cstephen...@gmail.com> > wrote: > > > > 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 > >> >
