Обсуждение: 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

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@gmail.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.albe@cybertec.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
>>

When I do serious database development I try to use database functions
as much as possible.

You can attach any flag value to a function in which case it gets set
when the function is running,

In your case you could probably wrap your query into an set-returning
`LANGUAGE SQL` function [1] and then include

`SET enable_material=false`

as part of the `CREATE FUNCTION` [2]

------
[1] https://www.postgresql.org/docs/current/xfunc-sql.html
[2] https://www.postgresql.org/docs/13/sql-createfunction.html

On Tue, Mar 23, 2021 at 4:22 PM Chris Stephens <cstephens16@gmail.com> wrote:
>
> "set enable_material=false;" produces an efficient plan. good to know there are *some* knobs to turn when the
optimizercomes up with a bad plan. would be awesome if you could lock that plan into place w/out altering the
variable.
>
> thanks for the help Hannu!
>
> On Mon, Mar 22, 2021 at 4:39 PM Hannu Krosing <hannuk@google.com> wrote:
>>
>> 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@gmail.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
aboutit.
 
>> >
>> > chris
>> >
>> >
>> > On Mon, Mar 22, 2021 at 9:54 AM Laurenz Albe <laurenz.albe@cybertec.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
>> >>