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:[email protected]]
Sent: May 03, 2015 9:43 AM
To: Mitu Verma
Cc: '[email protected]' ([email protected])
Subject: Re: [GENERAL] delete is getting hung when there is a huge data in table
Mitu Verma <[email protected]> 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
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
