From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Chris Stephens <cstephens16@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
Date: Mon, 22 Mar 2021 15:54:13 +0100
Message-ID: <aed084845c68eb61ffe1c31938f7dca80bc6abb4.camel@cybertec.at> (raw)
In-Reply-To: <CAEFL0sxgEFF8oCtTWEvz0_fyo75V4bqcnZqH=rh7qCYoj7JtSw@mail.gmail.com>
References: <CAEFL0sxgEFF8oCtTWEvz0_fyo75V4bqcnZqH=rh7qCYoj7JtSw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: laurenz.albe@cybertec.at, cstephens16@gmail.com
Subject: Re: SQL performance issue (postgresql chooses a bad plan when a better one is available)
In-Reply-To: <aed084845c68eb61ffe1c31938f7dca80bc6abb4.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox