<!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.16608" name=GENERATOR></HEAD>
<BODY>
<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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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><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>