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 <cstephen...@gmail.com> wrote: > > "set enable_material=false;" produces an efficient plan. good to know there > are *some* knobs to turn when the optimizer comes 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 <han...@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 <cstephen...@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.a...@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 >> >>
