Re: delete is getting hung when there is a huge data in table

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.

fm_db_Server1-> \d+ audittraillogentry
Table "mmsuper.audittraillogentry"
Column | Type | Modifiers | Storage | Description
--------------------------+-----------------------------+-----------+----------+-------------
event | smallint | | plain |
innodeid | character varying(80) | | extended |
innodename | character varying(80) | | extended |
sourceid | character varying(300) | | extended |
intime | timestamp without time zone | | plain |
outnodeid | character varying(80) | | extended |
outnodename | character varying(80) | | extended |
destinationid | character varying(300) | | extended |
outtime | timestamp without time zone | | plain |
bytes | bigint | | plain |
cdrs | bigint | | plain |
tableindex | bigint | not null | plain |
noofsubfilesinfile | bigint | | plain |
recordsequencenumberlist | character varying(1000) | | extended |
Indexes:
"audittraillogentry_pkey" PRIMARY KEY, btree (tableindex), tablespace "mmdata"
"audit_destid_index" btree (destinationid), tablespace "mmindex"
"audit_intime_index" btree (intime DESC), tablespace "mmindex"
"audit_outtime_index" btree (outtime DESC), tablespace "mmindex"
"audit_sourceid_index" btree (sourceid), tablespace "mmindex"
Referenced by:
TABLE "cdrdetails" CONSTRAINT "audittableindex_fkey" FOREIGN KEY (audittableindex) REFERENCES audittraillogentry(tableindex) ON DELETE CASCADE
TABLE "cdrlogentry" CONSTRAINT "cdrlogentry_audittableindex_fkey" FOREIGN KEY (audittableindex) REFERENCES audittraillogentry(tableindex)
Has OIDs: no
Tablespace: "mmdata"

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: May 03, 2015 9:43 AM
To: Mitu Verma
Cc: 'pgsql-general(at)postgresql(dot)org' (pgsql-general(at)postgresql(dot)org)
Subject: Re: [GENERAL] delete is getting hung when there is a huge data in table

Mitu Verma <mitu(dot)verma(at)ericsson(dot)com> writes:
> 1. If postgreSQL has some limitations for deletion of large data?

Not as such, but you've not given us any details that would permit comment.

A reasonably likely bet is that this table is referenced by a foreign key in some other table, and that other table has no index on the referencing column. That would make the FK is-it-ok-to-delete checks very slow.

regards, tom lane