delete taking long time - Mailing list pgsql-sql
| From | ivo liondov |
|---|---|
| Subject | delete taking long time |
| Date | |
| Msg-id | CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com Whole thread |
| Responses |
Re: delete taking long time
|
| List | pgsql-sql |
Table "public.connection"
Column | Type | Modifiers
-------------------+--------------------------------+-----------
uid | character varying(18) | not null
ts | timestamp(6) without time zone | not null
host_origin | inet | not null
port_origin | integer | not null
host_destination | inet | not null
port_destination | integer | not null
protocol | character varying(12) |
service | character varying(12) |
duration | interval second(6) |
origin_bytes | bigint |
response_bytes | bigint |
connection_state | character varying(8) |
local_origin | boolean |
local_response | boolean |
missed_bytes | bigint |
history | text |
origin_packets | bigint |
origin_ip_bytes | bigint |
response_packets | bigint |
response_ip_bytes | bigint |
Indexes:
"connection_pkey" PRIMARY KEY, btree (uid)
Foreign-key constraints:
"connection_protocol_fkey" FOREIGN KEY (protocol) REFERENCES protocol(name)
"connection_service_fkey" FOREIGN KEY (service) REFERENCES service(name)
Referenced by:
TABLE "dns" CONSTRAINT "dns_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "files" CONSTRAINT "files_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "http" CONSTRAINT "http_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "notice" CONSTRAINT "notice_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "snmp" CONSTRAINT "snmp_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "ssl" CONSTRAINT "ssl_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
TABLE "weird" CONSTRAINT "weird_uid_fkey" FOREIGN KEY (uid) REFERENCES connection(uid)
I am trying to delete the connections with date 2016-03-10 by using the following:
delete from connection where uid in (select uid from connection where ts > '2016-03-10 00:30:00');
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?
Regards.