Обсуждение: delete is extremely slow
Hello, Tried to run: delete from tableA where id <600000; It is extremely slow. id is integer and also the primary key. tableA has around 90000 records. (I do not want to run truncate since I have some foreign key constraint setup based on tableA. Besides not all data I want to delete; only part of them). Thanks
"Emi Lu" <emilu@encs.concordia.ca> wrote > Hello, > > Tried to run: > > delete from tableA where id <600000; > > It is extremely slow. > > id is integer and also the primary key. tableA has around 90000 records. > > (I do not want to run truncate since I have some foreign key constraint > setup based on tableA. Besides not all data I want to delete; only part > of them). > Since you have some FK constrains on A, so delete each line of A will confirm that it is not depended, which involves a lookup on the dependent table. Do you have indexes built on these dependents? Regards, Qingqing
>>Tried to run: >> >>delete from tableA where id <600000; >> >>It is extremely slow. >> >>id is integer and also the primary key. tableA has around 90000 records. >> >>(I do not want to run truncate since I have some foreign key constraint >>setup based on tableA. Besides not all data I want to delete; only part >>of them). >> >> >> > >Since you have some FK constrains on A, so delete each line of A will >confirm that it is not depended, which involves a lookup on the dependent >table. Do you have indexes built on these dependents? > No, I do not have indexes setup for those dependents. If I setup index for those columns, it will speed up the deletion, right? Thank you.
Emi Lu wrote: > >>> Tried to run: >>> >>> delete from tableA where id <600000; >>> >>> It is extremely slow. >>> >>> id is integer and also the primary key. tableA has around 90000 >>> records. >>> >>> (I do not want to run truncate since I have some foreign key constraint >>> setup based on tableA. Besides not all data I want to delete; only part >>> of them). >>> >>> >> >> >> Since you have some FK constrains on A, so delete each line of A will >> confirm that it is not depended, which involves a lookup on the >> dependent >> table. Do you have indexes built on these dependents? >> > > No, I do not have indexes setup for those dependents. > > If I setup index for those columns, it will speed up the deletion, right? > > Thank you. > > http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php If deletioin is 80% data, will index helps? Emi
On Fri, 2006-05-26 at 09:51, Emi Lu wrote: > Emi Lu wrote: > > > > >>> Tried to run: > >>> > >>> delete from tableA where id <600000; > >>> > >>> It is extremely slow. > >>> > >>> id is integer and also the primary key. tableA has around 90000 > >>> records. > >>> > >>> (I do not want to run truncate since I have some foreign key constraint > >>> setup based on tableA. Besides not all data I want to delete; only part > >>> of them). > >>> > >>> > >> > >> > >> Since you have some FK constrains on A, so delete each line of A will > >> confirm that it is not depended, which involves a lookup on the > >> dependent > >> table. Do you have indexes built on these dependents? > >> > > > > No, I do not have indexes setup for those dependents. > > > > If I setup index for those columns, it will speed up the deletion, right? > > > > Thank you. > > > > > http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php > > If deletioin is 80% data, will index helps? Yep. If your master table has 10,000 rows, and the dependent table has 100,000 rows, and you're deleting half of the master table (5,000 rows) then you'll have 5,000 sequential scans of the 100,000 row dependent table.
Scott Marlowe wrote: >On Fri, 2006-05-26 at 09:51, Emi Lu wrote: > > >>Emi Lu wrote: >> >> >> >>>>>Tried to run: >>>>> >>>>>delete from tableA where id <600000; >>>>> >>>>>It is extremely slow. >>>>> >>>>>id is integer and also the primary key. tableA has around 90000 >>>>>records. >>>>> >>>>>(I do not want to run truncate since I have some foreign key constraint >>>>>setup based on tableA. Besides not all data I want to delete; only part >>>>>of them). >>>>> >>>>> >>>>> >>>>> >>>>Since you have some FK constrains on A, so delete each line of A will >>>>confirm that it is not depended, which involves a lookup on the >>>>dependent >>>>table. Do you have indexes built on these dependents? >>>> >>>> >>>> >>>No, I do not have indexes setup for those dependents. >>> >>>If I setup index for those columns, it will speed up the deletion, right? >>> >>>Thank you. >>> >>> >>> >>> >>http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php >> >>If deletioin is 80% data, will index helps? >> >> > >Yep. If your master table has 10,000 rows, and the dependent table has >100,000 rows, and you're deleting half of the master table (5,000 rows) >then you'll have 5,000 sequential scans of the 100,000 row dependent >table. > > What about the reverse, 90,000 in the master table , while 500 records in each dependant tables?
On Fri, 2006-05-26 at 09:57, Emi Lu wrote: > Scott Marlowe wrote: > > >On Fri, 2006-05-26 at 09:51, Emi Lu wrote: > > > > > >>Emi Lu wrote: > >> > >> > >> > >>>>>Tried to run: > >>>>> > >>>>>delete from tableA where id <600000; > >>>>> > >>>>>It is extremely slow. > >>>>> > >>>>>id is integer and also the primary key. tableA has around 90000 > >>>>>records. > >>>>> > >>>>>(I do not want to run truncate since I have some foreign key constraint > >>>>>setup based on tableA. Besides not all data I want to delete; only part > >>>>>of them). > >>>>> > >>>>> > >>>>> > >>>>> > >>>>Since you have some FK constrains on A, so delete each line of A will > >>>>confirm that it is not depended, which involves a lookup on the > >>>>dependent > >>>>table. Do you have indexes built on these dependents? > >>>> > >>>> > >>>> > >>>No, I do not have indexes setup for those dependents. > >>> > >>>If I setup index for those columns, it will speed up the deletion, right? > >>> > >>>Thank you. > >>> > >>> > >>> > >>> > >>http://archives.postgresql.org/pgsql-general/2003-02/msg01615.php > >> > >>If deletioin is 80% data, will index helps? > >> > >> > > > >Yep. If your master table has 10,000 rows, and the dependent table has > >100,000 rows, and you're deleting half of the master table (5,000 rows) > >then you'll have 5,000 sequential scans of the 100,000 row dependent > >table. > > > > > What about the reverse, 90,000 in the master table , while 500 records > in each dependant tables? basically, for every row you delete in the master table, you'll have to sequentially scan every row in the dependent table(s) for each, if you don't have an index on the dependent table.