<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:m =
"http://schemas.microsoft.com/office/2004/12/omml"><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16441" name=GENERATOR><!--[if !mso]>
<STYLE>v\:* {
BEHAVIOR: url(#default#VML)
}
o\:* {
BEHAVIOR: url(#default#VML)
}
w\:* {
BEHAVIOR: url(#default#VML)
}
.shape {
BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>@font-face {
font-family: Calibri;
}
@font-face {
font-family: Tahoma;
}
@page Section1 {size: 595.3pt 841.9pt; margin: 70.85pt 85.05pt 70.85pt 85.05pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman","serif"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman","serif"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman","serif"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.EmailStyle17 {
COLOR: windowtext; FONT-FAMILY: "Arial","sans-serif"; mso-style-type: personal
}
SPAN.EmailStyle18 {
COLOR: #1f497d; FONT-FAMILY: "Calibri","sans-serif"; mso-style-type: personal-reply
}
.MsoChpDefault {
FONT-SIZE: 10pt; mso-style-type: export-only
}
DIV.Section1 {
page: Section1
}
</STYLE>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=868074715-06062007><FONT face=Arial color=#0000ff size=2>Before
postgres 8, count(*) query is known to be slow; however, starting from postgres
8, the count and and most aggregate queries run much faster - Thanks to the core
developers for fixing it.</FONT></SPAN></DIV>
<DIV><SPAN class=868074715-06062007><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=868074715-06062007><FONT face=Arial color=#0000ff
size=2>--</FONT></SPAN></DIV>
<DIV><SPAN class=868074715-06062007><FONT face=Arial color=#0000ff size=2>
Husam</FONT></SPAN></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>Rob
Tester<BR><B>Sent:</B> Wednesday, June 06, 2007 8:23 AM<BR><B>To:</B> 'PostGIS
Users Discussion'<BR><B>Subject:</B> RE: [postgis-users] Slow select from big
table<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV class=Section1>
<P class=MsoNormal><SPAN
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'">Sounds
more like a question for the general group. But, have you run a vacuum
analyze on the table ?<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN
style="FONT-SIZE: 11pt; COLOR: #1f497d; FONT-FAMILY: 'Calibri','sans-serif'"><o:p> </o:p></SPAN></P>
<DIV>
<DIV
style="BORDER-RIGHT: medium none; PADDING-RIGHT: 0in; BORDER-TOP: #b5c4df 1pt solid; PADDING-LEFT: 0in; PADDING-BOTTOM: 0in; BORDER-LEFT: medium none; PADDING-TOP: 3pt; BORDER-BOTTOM: medium none">
<P class=MsoNormal><B><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">From:</SPAN></B><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Tahoma','sans-serif'">
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Matt
Doughty<BR><B>Sent:</B> Wednesday, June 06, 2007 8:19 AM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Cc:</B> Sergio Jorrín
Abellán<BR><B>Subject:</B> [postgis-users] Slow select from big
table<o:p></o:p></SPAN></P></DIV></DIV>
<P class=MsoNormal><o:p> </o:p></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Hi
List,<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">I’ve got a simple
question, how can I speed up this query? <o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">SELECT COUNT (*) FROM
table1<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Currently it’s taking
a little more than five minutes to run on a table with 3.4 m rows. The table is
gist indexed. Is it normal that Postgres takes so long?<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Cheers,<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'">Matt<o:p></o:p></SPAN></P>
<P class=MsoNormal><SPAN lang=EN-GB
style="FONT-SIZE: 10pt; FONT-FAMILY: 'Arial','sans-serif'"><o:p> </o:p></SPAN></P>
<TABLE class=MsoNormalTable style="BACKGROUND: white; WIDTH: 100%" cellPadding=0
width="100%" align=left border=0>
<TBODY>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt"
colSpan=2>
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
lang=EN-GB> <o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt"
colSpan=2>
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
lang=EN-GB> <o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt">
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
lang=EN-GB style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">Matt
Doughty</SPAN><SPAN lang=EN-GB><o:p></o:p></SPAN></P></TD>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt"
vAlign=top rowSpan=5>
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><IMG
id=_x0000_i1025 height=100 src="cid:868074715@06062007-2153"
width=90><SPAN lang=EN-GB><o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt">
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
lang=EN-GB
style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">GEOGRAMA
</SPAN><SPAN
style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">S.L.</SPAN><SPAN
style="FONT-FAMILY: 'Arial','sans-serif'"><o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt">
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">Tel.: +34
945 13 13 72 652 77 14 15</SPAN><SPAN
style="FONT-FAMILY: 'Arial','sans-serif'"><o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt">
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">Fax: +34 945 23
03 40 </SPAN><SPAN
style="FONT-FAMILY: 'Arial','sans-serif'"><o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt">
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"><SPAN
style="COLOR: #2e3a75; FONT-FAMILY: 'Arial','sans-serif'">www.geograma.com</SPAN><SPAN
style="FONT-FAMILY: 'Arial','sans-serif'"><o:p></o:p></SPAN></P></TD></TR>
<TR>
<TD
style="PADDING-RIGHT: 0.75pt; PADDING-LEFT: 0.75pt; PADDING-BOTTOM: 0.75pt; PADDING-TOP: 0.75pt"
colSpan=2>
<P class=MsoNormal
style="mso-element: frame; mso-element-frame-hspace: 2.25pt; mso-element-wrap: around; mso-element-anchor-vertical: paragraph; mso-element-anchor-horizontal: column; mso-height-rule: exactly"> <o:p></o:p></P></TD></TR></TBODY></TABLE>
<P class=MsoNormal><SPAN
lang=ES><o:p> </o:p></SPAN></P></DIV>
<P>**********************************************************************<BR>This
message contains confidential information intended only for the use of the
addressee(s) named above and may contain information that is legally
privileged. If you are not the addressee, or the person responsible for
delivering it to the addressee, you are hereby notified that reading,
disseminating, distributing or copying this message is strictly
prohibited. If you have received this message by mistake, please
immediately notify us by replying to the message and delete the original message
immediately thereafter.</P>
<P>Thank you.</P>
<P><FONT
size=1>
FADLD
Tag</FONT><BR>**********************************************************************</P>
</BODY></HTML>