Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)

"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 <hannuk(at)google(dot)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 <cstephens16(at)gmail(dot)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(dot)albe(at)cybertec(dot)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
> >>
>