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

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