Eric Raskin <eraskin@xxxxxxxxxxxx> writes: > And, to follow up on your question, the plan shape DOES change when I > add/remove the nextval() on a plain explain. > Without nextval(): https://explain.depesz.com/s/SCdY > With nextval(): https://explain.depesz.com/s/oLPn Ah, there's your problem, I think: the plan without nextval() is parallelized while the plan with nextval() is not, because nextval() is marked as parallel-unsafe. It's not immediately clear why that would result in more than about a 4X speed difference, given that the parallel plan is using 4 workers. But some of the rowcount estimates seem fairly far off, so I'm betting that the planner is just accidentally lighting on a decent plan when it's using parallelism while making some poor choices when it isn't. The reason for the original form of your problem is likely that we don't use parallelism at all in non-SELECT queries, so you ended up with a bad plan even though the nextval() was hidden in a trigger. What you need to do is get the rowcount estimates nearer to reality --- those places where you've got estimated rowcount 1 while reality is tens or hundreds of thousands of rows are just disasters waiting to bite. I suspect most of the problem is join conditions like Join Filter: (CASE WHEN (c.rtype = ANY ('{0,1,7,9}'::bpchar[])) THEN c.rtype ELSE x.rtype END = '2'::bpchar) The planner just isn't going to have any credible idea how selective that is. I wonder to what extent you could fix this by storing generated columns that represent the derived conditions you want to filter on. regards, tom lane
