| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | Chris Stephens <cstephens16(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: SQL performance issue (postgresql chooses a bad plan when a better one is available) |
| Date: | 2021-03-22 14:54:13 |
| Message-ID: | aed084845c68eb61ffe1c31938f7dca80bc6abb4.camel@cybertec.at |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Chris Stephens | 2021-03-22 17:28:49 | Re: SQL performance issue (postgresql chooses a bad plan when a better one is available) |
| Previous Message | Chris Stephens | 2021-03-22 13:10:46 | SQL performance issue (postgresql chooses a bad plan when a better one is available) |