Re: Planning performance problem (67626.278ms)
On Thu, 1 Jul 2021 at 08:56, Manuel Weitzman wrote: > For each of these RestrictInfos there *could* be one cache miss on > cached_scansel() that *could* force the planner to compute > get_actual_variable_range() for the same variable (a.a) over and over, > as mergejoinscansel() always computes the selectivity for the > intervals that require actual extremal values. In practice this > re-computing of the variable range seems to happen a lot. Recently, for some other topic, I was thinking about if we were ever to have the executor give up on a plan because something did not play out the way the planner expected it to, that if the executor to ever be given that ability to throw the plan back at the planner with some hints about where it went wrong, then I wondered what exactly these hints would look like. I guessed these would be a list of some rough guidelines saying that x JOIN y ON x.a=y.b produces at least Z rows. Then the planner would find that when estimating the size of the join between x and y and take the maximum of two values. That would need to be designed in such a way that the planner could quickly consult the feedback, e.g hashtable lookup on Oid. Anyway, I don't really have any clearly thought-through plans for that idea as it would be a large project that would need a lot of changes before it could be even thought about seriously. However, it did cause me to think about that again when reading this thread as it seems similar. You've learned the actual variable range through actual execution, so it does not seem too unreasonable that information might get stored in a similar place, if that place were to exist. I'm not saying that's what needs to happen here. It's more just food for thought. The caching you need does seem more specific to Oid than relid. David
Re: slow performance with cursor
On 7/1/21 10:25 PM, Ayub Khan wrote: > Justin, > > Below is the stored procedure, is there any scope for improvement? > Hard to say, based on just the stored procedure source code. The queries are not too complex, but we don't know which of them gets selected for each cursor, and which of them is the slow one. I suggest you identify which of the cursors is the most problematic one, and focus on investigating it alone. Show us the explain analyze for that query with different cursor_tuple_fraction values and without the cursort, and so on. As Tom said, for a cursor the optimizer may be picking a plan with low startup cost, on the basis that that's good for a cursor. But if you're always consuming all the tuples, that may be inefficient. It's often an issue for queries with LIMIT, but none of the queries you include that clause, so who knows ... Try identifying which of the cursors is causing the issues, show us the explain analyze for that query (with and without the cursor), and that should tell us more. regards -- Tomas Vondra EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
