On Sun, 25 Feb, 2024, 1:05 am yudhi s, <learnerdatabas...@gmail.com> wrote:
> > On Fri, Feb 23, 2024 at 5:26 PM sud <suds1...@gmail.com> wrote: > >> >> >> On Fri, 23 Feb, 2024, 1:28 pm yudhi s, <learnerdatabas...@gmail.com> >> wrote: >> >>> >>> >>> On Fri, 23 Feb, 2024, 1:20 pm sud, <suds1...@gmail.com> wrote: >>> >>>> >>>> >>>> On Fri, 23 Feb, 2024, 12:41 pm Laurenz Albe, <laurenz.a...@cybertec.at> >>>> wrote: >>>> >>>>> On Fri, 2024-02-23 at 02:05 +0530, yudhi s >>>>> >>>>> > 2)Should we be creating composite indexes on each foreign key for >>>>> table2 and table3, because >>>>> > any update or delete on parent is going to take lock on all child >>>>> tables? >>>>> >>>>> Every foreign key needs its own index. A composite index is only >>>>> appropriate if the foreign >>>>> key spans multiple columns. >>>>> >>>>> >>>> >>>> From the DDL which OP posted it's using composite foreign key thus a >>>> composite index would be needed. >>>> However, if someone doesn't delete or update the parent table PK , is >>>> it still advisable to have all the FK indexed? Like in general I think >>>> transaction id should not get updated in a normal scenario unless some >>>> special case. >>>> >>>> >>>> >>> Thank you. I can double check if we have confirmed use case of deleting >>> the parent table or updating PK in the parent table. But anyway it can >>> happen for data fix for sure in some scenario. >>> >>> But yes, we are certainly going to drop/purge partition from all the >>> parent and child table after specific days. So isn't that need the FK to be >>> indexed or else it will scan whole parent table partition? >>> >> >> >> I am not sure if drop partition of parent table, will have a lock or will >> do a full scan on the child table while doing the partition maintenance or >> dropping the partitions, in absence of foreign key index. Others may >> comment here. >> >>> >>>> > Can you please help me understand, If it's true that all the statements > like Delete, Update and Drop partition of parent table will take lock on > the child table and Full scan the child table , and thus foreign key index > on all the child table is necessary irrespective of the performance > overhead it has on all the INSERT queries into the child tables? > Not sure of the lock but I think you should be able to see a full scan on child table while doing delete or update of parent table PK. Explain Analyze should show that I believe. Not sure if explain analyze will work for drop partition too. >
