[postgis-users] spatial query don't use index!

[Paragon Corporation]

Which version of postgis are you using?
 
SELECT postgis_full_version();
 
One thing I see somewhat suspicious from your prior query
 
 SELECT * FROM mapfriends."user" where    geometry @
mapfriends.ST_BUFFER(mapfriends.geometryfromtext('POINT(119.58
31.99175)'),0.1);
 
It appears you have postgis installed in a named schema -- mapfriends.
 
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.
 
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)
 
Should look in your case something like
 
CREATE OPERATOR CLASS mapfriends.gist_geometry_ops DEFAULT
   FOR TYPE geometry USING gist AS
   OPERATOR 1  << RECHECK,
   OPERATOR 2  &< RECHECK,
   OPERATOR 3  && RECHECK,
   OPERATOR 4  &> RECHECK,
   OPERATOR 5  >> RECHECK,
   OPERATOR 6  ~= RECHECK,
   OPERATOR 7  ~ RECHECK,
   OPERATOR 8  @ RECHECK,
   OPERATOR 9  &<| RECHECK,
   OPERATOR 10  <<| RECHECK,
   OPERATOR 11  |>> RECHECK,
   OPERATOR 12  |&> RECHECK,
   FUNCTION 1  mapfriends.lwgeom_gist_consistent(internal, geometry,
integer),
   FUNCTION 2  mapfriends.lwgeom_gist_union(bytea, internal),
   FUNCTION 3  mapfriends.lwgeom_gist_compress(internal),
   FUNCTION 4  mapfriends.lwgeom_gist_decompress(internal),
   FUNCTION 5  mapfriends.lwgeom_gist_penalty(internal, internal, internal),
   FUNCTION 6  mapfriends.lwgeom_gist_picksplit(internal, internal),
   FUNCTION 7  mapfriends.lwgeom_gist_same(box2d, box2d, internal)
   STORAGE box2d;
 
Hope that helps,
Regina
 
 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
yishh.lee
Sent: Thursday, June 18, 2009 3:50 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] spatial query don't use index!


 
Thanks.
This table size is 1200000 rows.
I create index originally:
CREATE INDEX user_idx_gin
  ON mapfriends."user"
  USING gist
  ((geometry));
 
now I replace with:
CREATE INDEX user_idx_gin
  ON mapfriends."user"
  USING gist
  ((geometry::box));
 
And query can use spatial index now.
 
Who can explain this?
2009-06-18 
  _____  

yishh.lee 
  _____  

发件人： Guillaume Lelarge 
发送时间： 2009-06-18  15:40:32 
收件人： PostGIS Users Discussion 
抄送： 
主题： Re: [postgis-users] spatial query don't use index! 
Suhr, Ralf a écrit :
> Your cost begin at value 0.00. The index is in use.
>  
The startup cost never shows if the index is in use or no. This is a
sequential scan, and no index is in use.
What is the size of the user table?
-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090618/2ef79930/attachment.html>