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

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
>
>