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

<div dir="ltr"><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><div dir="ltr"
style="font-family:arial,sans-serif;font-size:12.8px"><divclass="gmail_default" style="font-family:'comic sans
ms',sans-serif">HiAndreas,</div><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><br
/></div><divclass="gmail_default" style="font-family:'comic sans ms',sans-serif">shared_buffers is 512 MB. </div><div
class="gmail_default"style="font-family:'comic sans ms',sans-serif">I had to reduce the ts to only 10 minutes, just to
beable to finish promptly. Results are bellow.</div><div class="gmail_default" style="font-family:'comic sans
ms',sans-serif"><br/></div><div class="gmail_default" style="font-family:'comic sans ms',sans-serif"><p>select
count(uid)from connection where uid in (select uid from connection where ts > '2016-03-10 01:00:00' and ts <
'2016-03-1001:10:00');<p>count <p>-------<p>  2156<p><p>(1 row)<p><br /><p><p>explain (analyze) delete from connection
whereuid in (select uid from connection where ts > '2016-03-10 01:00:00' and ts < '2016-03-10 01:10:00');<p><br
/><p>-------------------------------------------------------------------------------------------------------------------------------------------------<p> Delete
onconnection  (cost=0.43..174184.31 rows=7756 width=12) (actual time=529.739..529.739 rows=0 loops=1)<p>   -> 
NestedLoop  (cost=0.43..174184.31 rows=7756 width=12) (actual time=0.036..526.295 rows=2156 loops=1)<p>         -> 
SeqScan on connection connection_1  (cost=0.00..115684.55 rows=7756 width=24) (actual time=0.020..505.012 rows=2156
loops=1)<p>              Filter: ((ts > '2016-03-10 01:00:00'::timestamp without time zone) AND (ts < '2016-03-10
01:10:00'::timestampwithout time zone))<p>               Rows Removed by Filter: <a href="tel:3108811" target="_blank"
value="+13108811">3108811</a><p>        ->  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)<p>               Index Cond: ((uid)::text =
(connection_1.uid)::text)<p> Planningtime: 0.220 ms<p> Trigger for constraint dns_uid_fkey: time=133.046
calls=2156<p> Triggerfor constraint files_uid_fkey: time=39780.799 calls=2156<p> Trigger for constraint http_uid_fkey:
time=99300.851calls=2156<p> Trigger for constraint notice_uid_fkey: time=128.653 calls=2156<p> Trigger for constraint
snmp_uid_fkey:time=59.491 calls=2156<p> Trigger for constraint ssl_uid_fkey: time=74.052 calls=2156<p> Trigger for
constraintweird_uid_fkey: time=25868.651 calls=2156<p> Execution time: 165880.419 ms<p><p>(16 rows)<p><br /><p><span
class="im"style="font-family:arial,sans-serif;font-size:12.8px">><br />> Given the lack of indexes on the one
tablethat is shown I suspect this is<br />> the most likely cause (FK + indexes)<br />><br /><br /></span><span
style="font-family:arial,sans-serif;font-size:12.8px">>right, there should be an index on ts. If it is a 9.5 AND the
rowsare ordered</span><br style="font-family:arial,sans-serif;font-size:12.8px" /><span
style="font-family:arial,sans-serif;font-size:12.8px">>on ts a BRIN-index would be fine.</span><br
style="font-family:arial,sans-serif;font-size:12.8px"/><span style="font-family:arial,sans-serif;font-size:12.8px">>
Ifnot - a normal index. How many rows contains the table?</span><br /></div></div><div class=""
style="font-family:arial,sans-serif;font-size:12.8px"></div><divclass=""
style="font-family:arial,sans-serif;font-size:12.8px"><pclass=""><span class=""> count  </span><p class=""><span
class="">---------</span><pclass=""><span class=""> 3108811</span><p class=""><span class="">(1 row)</span></div><div
class=""style="font-family:arial,sans-serif;font-size:12.8px"><p class="MsoNormal" style="font-size:12.8px"><span
style="font-size:11pt;font-family:Calibri,sans-serif">>There are SEVEN FKs against that table…I would bet that’s 50%
ofthe duration.  The lack of an index, </span><p class="MsoNormal" style="font-size:12.8px"><span
style="font-size:11pt;font-family:Calibri,sans-serif">>perhaps an issue, but<u></u><u></u></span><p
class="MsoNormal"style="font-size:12.8px"><span style="font-size:11pt;font-family:Calibri,sans-serif">> With that
manyFK references plus that many rows…the transaction log could easily blow out and start </span><p class="MsoNormal"
style="font-size:12.8px"><spanstyle="font-size:11pt;font-family:Calibri,sans-serif">> paging to disk.</span><p
class="MsoNormal"style="font-size:12.8px"><span style="font-size:11pt;font-family:Calibri,sans-serif"><br /></span><p
class="MsoNormal"style="font-size:12.8px">I think you are right, fk seem to take the biggest chunk of time from the
holedelete operation. I made a test with 10.000 rows, it took 12 minutes. 20.000 rows took about 25 minutes to
delete. <pclass="MsoNormal" style="font-size:12.8px"><br /><p class="MsoNormal" style="font-size:12.8px">Regards to
all.</div></div></div><divclass="gmail_extra"><br /><div class="gmail_quote">On 16 March 2016 at 01:12, Andreas
Kretschmer<span dir="ltr"><<a href="mailto:andreas@a-kretschmer.de"
target="_blank">andreas@a-kretschmer.de</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex"><span class=""><br /> ><br /> > I am trying to delete the
connectionswith date 2016-03-10 by using the<br /> > following:<br /> ><br /> ><br /> > delete from
connectionwhere uid in (select uid from connection where ts ><br /> > '2016-03-10 00:30:00');<br /><br
/></span>tryto rewrite that to :<br /><br /> delete from connection where ts > '2016-03-10 00:30:00';<br /><br />
It'ssimpler - and (maybe) faster.<br /><span class=""><br /><br /><br /> > There are around 800.000 records matching
thisrule, and seems to be taking<br /> > an awful lot of time - 4 hours and counting. What could be the reason
for<br/> > such a performance hit and how could I optimise this for future cases?<br /> ><br /> > Regards.<br
/><br/></span>the db has to touch such many rows, and has to write the transaction log. And<br /> update every index.
Andit has to check the referenced tables for the<br /> constraints. Do you have proper indexes?<br /><br /> How large
isshared_buffers set?<br /><br /> Can you show us the explain (analyse)?<br /><br /><br /> Regards, Andreas<br /><span
class="HOEnZb"><fontcolor="#888888">--<br /> Andreas Kretschmer<br /><a href="http://www.2ndQuadrant.com/"
rel="noreferrer"target="_blank">http://www.2ndQuadrant.com/</a><br /> PostgreSQL Development, 24x7 Support, Remote DBA,
Training& Services<br /></font></span></blockquote></div><br /></div>