Re: delete taking long time - Mailing list pgsql-sql

<div dir="ltr"><div class="gmail_default" style="font-family:'comic sans ms',sans-serif">My bad, I assumed postgres
createsindexes on foreign keys automatically. </div><div class="gmail_default" style="font-family:'comic sans
ms',sans-serif">Icreated indexes on all fk and on ts and OMG:</div><div class="gmail_default" style="font-family:'comic
sansms',sans-serif"><br /></div><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><p
class=""><spanclass="">bro=# select count(ts) from connection where ts >'2016-03-10 00:00:00';</span><p
class=""><spanclass=""> count  </span><p class=""><span class="">--------</span><p class=""><span
class=""> 546997</span><pclass=""><span class="">(1 row)</span><p class=""><span class=""></span><br /><p
class=""><spanclass="">bro=# select now(); delete from connection where uid in (select uid from connection where ts
>'2016-03-10 00:00:00' ); select now();</span><p class=""><span class="">              now              </span><p
class=""><spanclass="">-------------------------------</span><p class=""><span class=""> 2016-03-16
14:02:43.172617+00</span><pclass=""><span class="">(1 row)</span><p class=""><span class=""></span><br /><p
class=""><spanclass="">DELETE 546997</span><p class=""><span class="">              now              </span><p
class=""><spanclass="">-------------------------------</span><p class=""><span class=""> 2016-03-16
14:04:25.608695+00</span><pclass=""><span class="">(1 row)</span><p class=""><span class=""><br /></span><p
class=""><spanclass="">Took less than 2 minutes. Thanks for the help.</span></div></div><div class="gmail_extra"><br
/><divclass="gmail_quote">On 16 March 2016 at 11:57, Andreas Kretschmer <span dir="ltr"><<a
href="mailto:akretschmer@spamfence.net"target="_blank">akretschmer@spamfence.net</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">ivo liondov
<<ahref="mailto:ivo.liondov@gmail.com">ivo.liondov@gmail.com</a>> wrote:<br /><br /> ><br /> > explain
(analyze)delete from connection where uid in (select uid from<br /> > connection where ts > '2016-03-10 01:00:00'
andts < '2016-03-10 01:10:00');<br /> ><br /> ><br /> >
-------------------------------------------------------------------------------------------------------------------------------------------------<br
/>><br /> >  Delete on connection  (cost=0.43..174184.31 rows=7756 width=12) (actual time=<br /> >
529.739..529.739rows=0 loops=1)<br /> ><br /> >    ->  Nested Loop  (cost=0.43..174184.31 rows=7756 width=12)
(actualtime=<br /> > 0.036..526.295 rows=2156 loops=1)<br /> ><br /> >          ->  Seq Scan on connection
connection_1 (cost=0.00..115684.55 rows=<br /> > 7756 width=24) (actual time=0.020..505.012 rows=2156 loops=1)<br />
><br/> >                Filter: ((ts > '2016-03-10 01:00:00'::timestamp without time<br /> > zone) AND (ts
<'2016-03-10 01:10:00'::timestamp without time zone))<br /><br /><br /></span>there is no index on the ts-column.<br
/><spanclass=""><br /><br /><br /><br /><br /><br /><br /><br /> ><br /> >                Rows Removed by
Filter: <ahref="tel:3108811" value="+13108811">3108811</a><br /> ><br /> >          ->  Index Scan using
connection_pkeyon connection  (cost=0.43..7.53<br /> > rows=1 width=24) (actual time=0.009..0.010 rows=1
loops=2156)<br/> ><br /> >                Index Cond: ((uid)::text = (connection_1.uid)::text)<br /> ><br />
> Planning time: 0.220 ms<br /> ><br /> >  Trigger for constraint dns_uid_fkey: time=133.046 calls=2156<br />
><br/> >  Trigger for constraint files_uid_fkey: time=39780.799 calls=2156<br /> ><br /> >  Trigger for
constrainthttp_uid_fkey: time=99300.851 calls=2156<br /> ><br /> >  Trigger for constraint notice_uid_fkey:
time=128.653calls=2156<br /> ><br /> >  Trigger for constraint snmp_uid_fkey: time=59.491 calls=2156<br />
><br/> >  Trigger for constraint ssl_uid_fkey: time=74.052 calls=2156<br /> ><br /> >  Trigger for
constraintweird_uid_fkey: time=25868.651 calls=2156<br /> ><br /> >  Execution time: 165880.419 ms<br /><br
/></span>iguess there are no indexes for this tables and the relevant columns<br /><span class=""><br /><br /> > I
thinkyou are right, fk seem to take the biggest chunk of time from the hole<br /> > delete operation. I made a test
with10.000 rows, it took 12 minutes. 20.000<br /> > rows took about 25 minutes to delete. <br /><br /></span>create
themissing indexes now and come back with the new duration.<br /><br /><br /> Andreas<br /><span class="HOEnZb"><font
color="#888888">--<br/> Really, I'm not out to destroy Microsoft. That will just be a completely<br /> unintentional
sideeffect.                              (Linus Torvalds)<br /> "If I was god, I would recompile penguin with
--enable-fly."  (unknown)<br /> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°<br /><br /><br
/>--<br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br
/>To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql" rel="noreferrer"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></span></blockquote></div><br /></div>