<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [postgis-users] RE: Slow vacuuming</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16640" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left>
<DIV dir=ltr align=left><SPAN class=734131423-13042008><FONT face=Arial
color=#0000ff size=2>Update. My vacuum is running fine now. I posted
the same issue to the PostgreSQL newsgroup. Turns out I was just being
really stupid and had set my vacuum costs in my postgresql.conf to never use any
IO. Setting these back to default makes vacuum run within seconds
now.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=734131423-13042008><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=734131423-13042008><FONT face=Arial
color=#0000ff size=2>Thanks,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=734131423-13042008><FONT face=Arial
color=#0000ff size=2>Regina</FONT></SPAN></DIV></DIV><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Paragon<BR><B>Sent:</B> Thursday, April 10, 2008 11:39 PM<BR><B>To:</B>
'PostGIS Users Discussion'<BR><B>Subject:</B> RE: [postgis-users] RE: Slow
vacuuming<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><SPAN
class=164022603-11042008></SPAN><FONT face=Arial color=#0000ff size=2>U<SPAN
class=164022603-11042008>pping my work_mem didn't seem to make a difference
although it improved my query speed on some other
things. </SPAN></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT face=Arial
color=#0000ff size=2><SPAN
class=164022603-11042008></SPAN></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>vacuum analyze fl.fl_edges; </SPAN><BR><SPAN
class=164022603-11042008> </SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=164022603-11042008>I
ruled out clustering as the issue since I have dropped all indexes on that table
before I reran vacuum
analyze.</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=164022603-11042008>I
thought maybe there was some corruption somewhere so I had copied that data to a
temp table with a bulk
insert</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>SELECT * INTO fl.fl_edges_temp FROM
fl.fl_edges</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>(which ran in about 80 secs) - total records
2,231,947</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=164022603-11042008>Then
I did a </SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>truncate table
fl.fl_eges</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=164022603-11042008>and
repopulated which took about 90 seconds. And now vacuuming patiently for
the past 15 minutes or
so.</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN class=164022603-11042008>Any
other
thoughts,</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>Thanks</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>Regina</SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT></FONT></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=+0><SPAN class=835303202-11042008><SPAN
class=835303202-11042008><SPAN class=835303202-11042008><FONT size=+0><FONT
face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=164022603-11042008></SPAN></FONT></FONT></FONT> </DIV></FONT></SPAN></SPAN><FONT
face=Arial color=#0000ff size=2></FONT></SPAN></FONT>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=835303202-11042008><FONT face=Arial><FONT
color=#0000ff><FONT size=2><SPAN class=164022603-11042008> >
</SPAN>I'm going to up my work_mem once my current state load is done or
at least at a safe breaking point.</FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008><SPAN class=164022603-11042008>>
</SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><SPAN class=835303202-11042008><FONT face=Arial><FONT
color=#0000ff><FONT size=2><SPAN class=164022603-11042008>>
</SPAN>Thanks,</FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=835303202-11042008><FONT face=Arial><FONT
color=#0000ff><FONT size=2><SPAN
class=164022603-11042008>> </SPAN>Regina</FONT></FONT></FONT></SPAN></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT size=2></FONT> </DIV></BODY></HTML>