<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=gb2312" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18783">
<STYLE>@font-face {
font-family: 宋体;
}
@font-face {
font-family: Verdana;
}
@font-face {
font-family: @宋体;
}
@page Section1 {size: 595.3pt 841.9pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; layout-grid: 15.6pt; }
P.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; TEXT-ALIGN: justify; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; FONT-SIZE: 10.5pt
}
LI.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; TEXT-ALIGN: justify; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; FONT-SIZE: 10.5pt
}
DIV.MsoNormal {
TEXT-JUSTIFY: inter-ideograph; TEXT-ALIGN: justify; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"; FONT-SIZE: 10.5pt
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.EmailStyle17 {
FONT-STYLE: normal; FONT-FAMILY: Verdana; COLOR: windowtext; FONT-WEIGHT: normal; TEXT-DECORATION: none; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
UNKNOWN {
FONT-SIZE: 10pt
}
BLOCKQUOTE {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px; MARGIN-LEFT: 2em
}
OL {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
UL {
MARGIN-TOP: 0px; MARGIN-BOTTOM: 0px
}
</STYLE>
</HEAD>
<BODY style="MARGIN: 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt">
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana><FONT
color=#c0c0c0><SPAN></SPAN></FONT></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>Which
version of postgis are you using?</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>SELECT
postgis_full_version();</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>One
thing I see somewhat suspicious from your prior query</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009> SELECT * FROM
mapfriends."user" where geometry @
mapfriends.ST_BUFFER(mapfriends.geometryfromtext('POINT(119.58
31.99175)'),0.1);</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>It
appears you have postgis installed in a named schema --
mapfriends.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>It
might be possible that you also have postgis installed in the public schema as
well and if you do, it is possible you have geometry type defined twice --- one
in mapfriends and one in public. These two geometry types would be treated
as different types and in that case, you may only have spatial index operators
defined for one of them.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>I
would verify to make sure you have only one install of PostGIS and that you have
gist_geometry_ops (Operator classes defined for that one)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>Should
look in your case something like</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT
face=verdana></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>CREATE
OPERATOR CLASS mapfriends.gist_geometry_ops DEFAULT<BR> FOR TYPE
geometry USING gist AS<BR> OPERATOR 1 <<
RECHECK,<BR> OPERATOR 2 &< RECHECK,<BR>
OPERATOR 3 && RECHECK,<BR> OPERATOR 4 &>
RECHECK,<BR> OPERATOR 5 >> RECHECK,<BR>
OPERATOR 6 ~= RECHECK,<BR> OPERATOR 7 ~
RECHECK,<BR> OPERATOR 8 @ RECHECK,<BR> OPERATOR
9 &<| RECHECK,<BR> OPERATOR 10 <<|
RECHECK,<BR> OPERATOR 11 |>> RECHECK,<BR>
OPERATOR 12 |&> RECHECK,<BR> FUNCTION 1
mapfriends.lwgeom_gist_consistent(internal, geometry, integer),<BR>
FUNCTION 2 mapfriends.lwgeom_gist_union(bytea, internal),<BR>
FUNCTION 3 mapfriends.lwgeom_gist_compress(internal),<BR>
FUNCTION 4 mapfriends.lwgeom_gist_decompress(internal),<BR>
FUNCTION 5 mapfriends.lwgeom_gist_penalty(internal, internal,
internal),<BR> FUNCTION 6
mapfriends.lwgeom_gist_picksplit(internal, internal),<BR> FUNCTION
7 mapfriends.lwgeom_gist_same(box2d, box2d, internal)<BR>
STORAGE box2d;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT face=verdana>Hope
that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009>Regina</SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=231312909-18062009><FONT
face=verdana></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT face=Tahoma><B>From:</B> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>yishh.lee<BR><B>Sent:</B> Thursday, June 18, 2009 3:50 AM<BR><B>To:</B>
PostGIS Users Discussion<BR><B>Subject:</B> Re: [postgis-users] spatial query
don't use index!<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT color=#000080 face=Verdana></FONT> </DIV>
<DIV><FONT color=#000080 face=Verdana>Thanks.</FONT></DIV>
<DIV>This table size is 1200000 rows.</DIV>
<DIV>I create index originally:</DIV>
<DIV>
<DIV>CREATE INDEX user_idx_gin</DIV>
<DIV> ON mapfriends."user"</DIV>
<DIV> USING gist</DIV>
<DIV> ((geometry));</DIV>
<DIV> </DIV></DIV>
<DIV><FONT color=#000080 face=Verdana>now I replace with:</FONT></DIV>
<DIV>
<DIV>CREATE INDEX user_idx_gin</DIV>
<DIV> ON mapfriends."user"</DIV>
<DIV> USING gist</DIV>
<DIV> ((geometry::box));</DIV></DIV>
<DIV><FONT color=#000080></FONT> </DIV>
<DIV><FONT color=#000080>And query can use spatial index now.</FONT></DIV>
<DIV><FONT color=#000080></FONT> </DIV>
<DIV><FONT color=#000080>Who can e</FONT><FONT color=#000000>xplain
this?</FONT></DIV>
<DIV><FONT color=#c0c0c0 face=Verdana>2009-06-18 </FONT></DIV><FONT
color=#000080 face=Verdana>
<HR style="WIDTH: 100px" align=left color=#b5c4df SIZE=1>
</FONT>
<DIV><FONT color=#c0c0c0 face=Verdana><SPAN>yishh.lee</SPAN> </FONT></DIV>
<HR color=#b5c4df SIZE=1>
<DIV><FONT face=Verdana><STRONG>发件人：</STRONG> Guillaume Lelarge </FONT></DIV>
<DIV><FONT face=Verdana><STRONG>发送时间：</STRONG> 2009-06-18 15:40:32
</FONT></DIV>
<DIV><FONT face=Verdana><STRONG>收件人：</STRONG> PostGIS Users Discussion
</FONT></DIV>
<DIV><FONT face=Verdana><STRONG>抄送：</STRONG> </FONT></DIV>
<DIV><FONT face=Verdana><STRONG>主题：</STRONG> Re: [postgis-users] spatial query
don't use index! </FONT></DIV>
<DIV><FONT face=Verdana></FONT></DIV>
<DIV><FONT face=Verdana>
<DIV>Suhr, Ralf a écrit :</DIV>
<DIV>> Your cost begin at value 0.00. The index is in use.</DIV>
<DIV>> </DIV>
<DIV></DIV>
<DIV>The startup cost never shows if the index is in use or no. This is a</DIV>
<DIV>sequential scan, and no index is in use.</DIV>
<DIV></DIV>
<DIV>What is the size of the user table?</DIV>
<DIV></DIV>
<DIV></DIV>
<DIV>-- </DIV>
<DIV>Guillaume.</DIV>
<DIV> http://www.postgresqlfr.org</DIV>
<DIV> http://dalibo.com</DIV>
<DIV>_______________________________________________</DIV>
<DIV>postgis-users mailing list</DIV>
<DIV>postgis-users@postgis.refractions.net</DIV>
<DIV>http://postgis.refractions.net/mailman/listinfo/postgis-users</DIV></FONT></DIV></BODY></HTML>