Re: making update/delete of inheritance trees scale better

On Wed, May 13, 2020 at 9:21 AM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
>
> Maybe I am misunderstanding you, but the more the rows to update, the
> more overhead we will be paying with the new approach.

Yes, that's right. How much is that compared to the current planning
overhead. How many rows it takes for that overhead to be comparable to
the current planning overhead.

But let's not sweat on that point much right now.

>
> So, we will need to do 2 things:
>
> 1. Implicitly apply an ORDER BY tableoid clause
> 2. Add result relation RTIs to ModifyTable.resultRelations in the
> order of their RTE's relid.
>
> Maybe we can do that as a separate patch. Also, I am not sure if it
> will get in the way of someone wanting to have ORDER BY LIMIT for
> updates.

It won't. But may be David's idea is better.

>
> > > > * Tuple re-routing during UPDATE. For now it's disabled so your design
> > > > should work. But we shouldn't design this feature in such a way that
> > > > it comes in the way to enable tuple re-routing in future :).
> > >
> > > Sorry, what is tuple re-routing and why does this new approach get in its way?
> >
> > An UPDATE causing a tuple to move to a different partition. It would
> > get in its way since the tuple will be located based on tableoid,
> > which will be the oid of the old partition. But I think this approach
> > has higher chance of being able to solve that problem eventually
> > rather than the current approach.
>
> Again, I don't think I understand. We do currently (as of v11)
> re-route tuples when UPDATE causes them to move to a different
> partition, which, gladly, continues to work with my patch.

Ah! Ok. I missed that part then.

>
> So how it works is like this: for a given "new" tuple, ExecUpdate()
> checks if the tuple would violate the partition constraint of the
> result relation that was passed along with the tuple. If it does, the
> new tuple will be moved, by calling ExecDelete() to delete it from the
> current relation, followed by ExecInsert() to find the new home for
> the tuple. The only thing that changes with the new approach is how
> ExecModifyTable() chooses a result relation to pass to ExecUpdate()
> for a given "new" tuple it has fetched from the plan, which is quite
> independent from the tuple re-routing mechanism proper.
>

Thanks for the explanation.

--
Best Wishes,
Ashutosh Bapat