Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx] 
Sent: May 03, 2015 9:43 AM
To: Mitu Verma
Cc: 'pgsql-general@xxxxxxxxxxxxxx' (pgsql-general@xxxxxxxxxxxxxx)
Subject: Re:  delete is getting hung when there is a huge data in table

Mitu Verma <mitu.verma@xxxxxxxxxxxx> 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 (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux