delete taking long time

<div dir="ltr"><div class="gmail_default" style="font-family:'comic sans ms',sans-serif">HI,</div><div
class="gmail_default"style="font-family:'comic sans ms',sans-serif"><br /></div><div class="gmail_default"
style="font-family:'comicsans ms',sans-serif">I have the following table:</div><div class="gmail_default"
style="font-family:'comicsans ms',sans-serif"><br /></div><div class="gmail_default" style="font-family:'comic sans
ms',sans-serif"><pclass=""><span class="">                   Table "public.connection"</span><p class=""><span
class="">     Column       |              Type              | Modifiers </span><p class=""><span
class="">-------------------+--------------------------------+-----------</span><pclass=""><span class=""> uid        
     | character varying(18)          | not null</span><p class=""><span class=""> ts                | timestamp(6)
withouttime zone | not null</span><p class=""><span class=""> host_origin       | inet                           | not
null</span><pclass=""><span class=""> port_origin       | integer                        | not null</span><p
class=""><spanclass=""> host_destination  | inet                           | not null</span><p class=""><span
class=""> port_destination | integer                        | not null</span><p class=""><span class=""> protocol     
   | character varying(12)          | </span><p class=""><span class=""> service           | character varying(12)     
   | </span><p class=""><span class=""> duration          | interval second(6)             | </span><p class=""><span
class=""> origin_bytes     | bigint                         | </span><p class=""><span class=""> response_bytes    |
bigint                        | </span><p class=""><span class=""> connection_state  | character varying(8)          
| </span><pclass=""><span class=""> local_origin      | boolean                        | </span><p class=""><span
class=""> local_response   | boolean                        | </span><p class=""><span class=""> missed_bytes      |
bigint                        | </span><p class=""><span class=""> history           | text                          
| </span><pclass=""><span class=""> origin_packets    | bigint                         | </span><p class=""><span
class=""> origin_ip_bytes  | bigint                         | </span><p class=""><span class=""> response_packets  |
bigint                        | </span><p class=""><span class=""> response_ip_bytes | bigint                        
| </span><pclass=""><span class="">Indexes:</span><p class=""><span class="">    "connection_pkey" PRIMARY KEY, btree
(uid)</span><pclass=""><span class="">Foreign-key constraints:</span><p class=""><span class="">   
"connection_protocol_fkey"FOREIGN KEY (protocol) REFERENCES protocol(name)</span><p class=""><span class="">   
"connection_service_fkey"FOREIGN KEY (service) REFERENCES service(name)</span><p class=""><span class="">Referenced
by:</span><pclass=""><span class="">    TABLE "dns" CONSTRAINT "dns_uid_fkey" FOREIGN KEY (uid) REFERENCES
connection(uid)</span><pclass=""><span class="">    TABLE "files" CONSTRAINT "files_uid_fkey" FOREIGN KEY (uid)
REFERENCESconnection(uid)</span><p class=""><span class="">    TABLE "http" CONSTRAINT "http_uid_fkey" FOREIGN KEY
(uid)REFERENCES connection(uid)</span><p class=""><span class="">    TABLE "notice" CONSTRAINT "notice_uid_fkey"
FOREIGNKEY (uid) REFERENCES connection(uid)</span><p class=""><span class="">    TABLE "snmp" CONSTRAINT
"snmp_uid_fkey"FOREIGN KEY (uid) REFERENCES connection(uid)</span><p class=""><span class="">    TABLE "ssl" CONSTRAINT
"ssl_uid_fkey"FOREIGN KEY (uid) REFERENCES connection(uid)</span><p class=""><span class="">    TABLE "weird"
CONSTRAINT"weird_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)</span><p class=""><span class=""><br
/></span><pclass=""><span class="">I am trying to delete the connections with date 2016-03-10 by using the
following:</span><pclass=""><span class=""><br /></span><p class=""><span class=""> </span><p class=""><span
class="">deletefrom connection where uid in (select uid from connection where ts > '2016-03-10 00:30:00');</span><p
class=""><spanclass="">There are around 800.000 records matching this rule, and seems to be taking an awful lot of time
-4 hours and counting. What could be the reason for such a performance hit and how could I optimise this for future
cases?</span><pclass=""><span class="">Regards.</span></div></div>