On Wed, May 6, 2015 at 1:56 AM, Mitu Verma <mitu.verma@xxxxxxxxxxxx> wrote: > Thank you so much all of you. > > Table audittraillogentry have PRIMARY KEY and FOREIGN KEY defined, below is the detail of existing table audittraillogentry. > > As you can see ., it is referenced by 2 tables , "cdrdetails" and "cdrlogentry" . "cdrdetails" table do not have the index whereas "cdrlogentry" has the index. > Now after creating the index on "cdrdetails", deletion has become fast, 12 lakh records are deleted in 16 minutes, which is a drastic improvement in performance. > Before indexing deletion of 500 records were taking ~2 minutes. Yeah, this (unindexed foreign key causing slow deletes) is probably the #1 performance gotcha in SQL. If you're often doing very large deletes, sometimes it can help to attempt to work out a better strategy, perhaps one of: *) using TRUNCATE...CASADE *) table partitioning organized such that you can drop a partition to delete rows *) temporarily disabling RI during large deletes (can be dangerous and but in certain limited cases can be useful). merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
