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

<div dir="ltr"><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><span
style="font-family:arial,sans-serif;font-size:12.8px">>And...from a db design perspective, a table referenced by 7
FKsshouldn't be</span><br style="font-family:arial,sans-serif;font-size:12.8px" /><span
style="font-family:arial,sans-serif;font-size:12.8px">>having this type</span><br
style="font-family:arial,sans-serif;font-size:12.8px"/><span style="font-family:arial,sans-serif;font-size:12.8px">>
ofdelete run against it...it's just too expensive if it must happen</span><br
style="font-family:arial,sans-serif;font-size:12.8px"/><span style="font-family:arial,sans-serif;font-size:12.8px">>
routinely. </span><br/></div><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><span
style="font-family:arial,sans-serif;font-size:12.8px"><br/></span></div><div class="gmail_default"
style="font-family:'comicsans ms',sans-serif"><span style="font-family:arial,sans-serif;font-size:12.8px">The
connectiontable should never have any data deleted, only insertions are performed.</span></div><div
class="gmail_default"><spanstyle="font-size:12.8px">I had logged using ssh to the db server, forgot to plugin my laptop
andit went to sleep mode while running an insert script from external data source - a bro ids file. The shell logged me
outand the insert statement never completed ( about 10 % of the data never got inserted). I should know
better.</span></div><divclass="gmail_default"><span style="font-size:12.8px"><br /></span></div><div
class="gmail_default"><spanstyle="font-size:12.8px">Kind regards to all of you.</span></div></div><div
class="gmail_extra"><br/><div class="gmail_quote">On 16 March 2016 at 12:31, Mike Sofen <span dir="ltr"><<a
href="mailto:msofen@runbox.com"target="_blank">msofen@runbox.com</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">I agree with Andreas (indexes)
-10 minutes to delete 10k rows is about 9.5<br /> minutes too long.<br /> Either the "select" part of the query can't
findthe rows quickly or the FK<br /> burden is crushing the life out of it.<br /><br /> If every involved table has an
indexon their Primary Key then the 10k row<br /> delete should take<br /> maybe 30-60 seconds.  Highly dependent on how
manyFK rows are involved.<br /><br /> And...from a db design perspective, a table referenced by 7 FKs shouldn't be<br
/>having this type<br /> of delete run against it...it's just too expensive if it must happen<br /> routinely.  This is
where<br/> de-normalization might be called for, to collapse some of those references,<br /> or a shift to<br /> stored
functionsthat maintain integrity versus the declared foreign keys<br /> maintaining it.<br /><br /> Mike S.<br /><div
class="HOEnZb"><divclass="h5"><br /> -----Original Message-----<br /> From: Andreas Kretschmer<br /> Sent: Wednesday,
March16, 2016 4:58 AM<br /><br /> ivo liondov <<a href="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 /> >
connectionwhere ts > '2016-03-10 01:00:00' and ts < '2016-03-10<br /> > 01:10:00');<br /> ><br /> ><br
/>> ----------------------------------------------------------------------<br /> >
----------------------------------------------------------------------<br/> > -----<br /> ><br /> >  Delete on
connection (cost=0.43..174184.31 rows=7756 width=12)<br /> > (actual time=<br /> > 529.739..529.739 rows=0
loops=1)<br/> ><br /> >    ->  Nested Loop  (cost=0.43..174184.31 rows=7756 width=12) (actual<br /> >
time=<br/> > 0.036..526.295 rows=2156 loops=1)<br /> ><br /> >          ->  Seq Scan on connection
connection_1 <br/> > (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<br /> >
time<br/> > zone) AND (ts < '2016-03-10 01:10:00'::timestamp without time zone))<br /><br /><br /> there is no
indexon the ts-column.<br /><br /><br /><br /><br /><br /><br /><br /><br /> ><br /> >                Rows
Removedby Filter: 3108811<br /> ><br /> >          ->  Index Scan using connection_pkey on connection <br />
>(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
constraintfiles_uid_fkey: time=39780.799 calls=2156<br /> ><br /> >  Trigger for constraint http_uid_fkey:
time=99300.851calls=2156<br /> ><br /> >  Trigger for constraint notice_uid_fkey: time=128.653 calls=2156<br />
><br/> >  Trigger for constraint snmp_uid_fkey: time=59.491 calls=2156<br /> ><br /> >  Trigger for
constraintssl_uid_fkey: time=74.052 calls=2156<br /> ><br /> >  Trigger for constraint weird_uid_fkey:
time=25868.651calls=2156<br /> ><br /> >  Execution time: 165880.419 ms<br /><br /> i guess there are no indexes
forthis tables and the relevant columns<br /><br /><br /> > I think you are right, fk seem to take the biggest chunk
oftime from<br /> > the hole delete operation. I made a test with 10.000 rows, it took 12<br /> > minutes. 20.000
rowstook about 25 minutes to delete.<br /><br /> create the missing indexes now and come back with the new duration.<br
/><br/><br /> Andreas<br /> --<br /> Really, I'm not out to destroy Microsoft. That will just be a completely<br />
unintentionalside effect.                              (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/><br /><br /><br /> --<br /> Sent via pgsql-sql
mailinglist (<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/></div></div></blockquote></div><br /></div>