Date: Wed, 16 Mar 2016 12:57:53 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: delete taking long time
Message-ID: <20160316115753.GA6081@tux>
References: 
 <CAJ2MONRiYczFWzL5_6bh3ZV_pcnN2zzg2Q+GDW1r_VXS=-SRtw@mail.gmail.com>
 <503958962.9182.1458090747344.JavaMail.open-xchange@oxweb01.ims-firmen.de>
 <CAJ2MONQoLp=pd5U4vYEuW072FSKvr=QJVBzpUgs6TjGFMQGGEQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAJ2MONQoLp=pd5U4vYEuW072FSKvr=QJVBzpUgs6TjGFMQGGEQ@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

ivo liondov <ivo.liondov@gmail.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°


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql