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

you can play around various `enable_*` flags to see if disabling any
of these will *maybe* yield the plan you were expecting, and then
check the costs in EXPLAIN to see if the optimiser also thinks this
plan is cheaper.

On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens <cstephens16(at)gmail(dot)com> wrote:
>
> 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
>>