From: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: delete taking long time |
Date: | 2016-03-16 11:57:53 |
Message-ID: | 20160316115753.GA6081@tux |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
ivo liondov <ivo(dot)liondov(at)gmail(dot)com> wrote:
>
> explain (analyze) delete from connection where uid in (select uid from
> connection where ts > '2016-03-10 01:00:00' and ts < '2016-03-10 01:10:00');
>
>
> -------------------------------------------------------------------------------------------------------------------------------------------------
>
> Delete on connection (cost=0.43..174184.31 rows=7756 width=12) (actual time=
> 529.739..529.739 rows=0 loops=1)
>
> -> Nested Loop (cost=0.43..174184.31 rows=7756 width=12) (actual time=
> 0.036..526.295 rows=2156 loops=1)
>
> -> Seq Scan on connection connection_1 (cost=0.00..115684.55 rows=
> 7756 width=24) (actual time=0.020..505.012 rows=2156 loops=1)
>
> Filter: ((ts > '2016-03-10 01:00:00'::timestamp without time
> zone) AND (ts < '2016-03-10 01:10:00'::timestamp without time zone))
there is no index on the ts-column.
>
> Rows Removed by Filter: 3108811
>
> -> Index Scan using connection_pkey on connection (cost=0.43..7.53
> rows=1 width=24) (actual time=0.009..0.010 rows=1 loops=2156)
>
> Index Cond: ((uid)::text = (connection_1.uid)::text)
>
> Planning time: 0.220 ms
>
> Trigger for constraint dns_uid_fkey: time=133.046 calls=2156
>
> Trigger for constraint files_uid_fkey: time=39780.799 calls=2156
>
> Trigger for constraint http_uid_fkey: time=99300.851 calls=2156
>
> Trigger for constraint notice_uid_fkey: time=128.653 calls=2156
>
> Trigger for constraint snmp_uid_fkey: time=59.491 calls=2156
>
> Trigger for constraint ssl_uid_fkey: time=74.052 calls=2156
>
> Trigger for constraint weird_uid_fkey: time=25868.651 calls=2156
>
> Execution time: 165880.419 ms
i guess there are no indexes for this tables and the relevant columns
> I think you are right, fk seem to take the biggest chunk of time from the hole
> delete operation. I made a test with 10.000 rows, it took 12 minutes. 20.000
> rows took about 25 minutes to delete.
create the missing indexes now and come back with the new duration.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Sofen | 2016-03-16 12:31:45 | Re: delete taking long time |
Previous Message | ivo liondov | 2016-03-16 11:06:38 | Re: delete taking long time |