Message-ID: <aed084845c68eb61ffe1c31938f7dca80bc6abb4.camel@cybertec.at>
Subject: Re: SQL performance issue (postgresql chooses a bad plan when a
 better one is available)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Stephens <cstephens16@gmail.com>, pgsql-performance@postgresql.org
Date: Mon, 22 Mar 2021 15:54:13 +0100
In-Reply-To: 
 <CAEFL0sxgEFF8oCtTWEvz0_fyo75V4bqcnZqH=rh7qCYoj7JtSw@mail.gmail.com>
References: 
	<CAEFL0sxgEFF8oCtTWEvz0_fyo75V4bqcnZqH=rh7qCYoj7JtSw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.36.5 (3.36.5-2.fc32) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/aed084845c68eb61ffe1c31938f7dca80bc6abb4.camel%40cybertec.at>
Precedence: bulk

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