Re: delete is getting hung when there is a huge data in table - Mailing list pgsql-general

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
indexwhereas "cdrlogentry" has the index. 
Now  after creating the index on "cdrdetails", deletion has become fast, 12 lakh records are deleted in 16 minutes,
whichis 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@sss.pgh.pa.us]
Sent: May 03, 2015 9:43 AM
To: Mitu Verma
Cc: 'pgsql-general@postgresql.org' (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] delete is getting hung when there is a huge data in table

Mitu Verma <mitu.verma@ericsson.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
noindex on the referencing column.  That would make the FK is-it-ok-to-delete checks very slow. 

            regards, tom lane