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

[yishh.lee]

Oh,Thanks very much.
My postgis version is 1.3.6 and install in both public and mapfriends.
I use your class,it works!


2009-06-18 



yishh.lee 



发件人： Paragon Corporation 
发送时间： 2009-06-18  17:38:32 
收件人： 'PostGIS Users Discussion' 
抄送： 
主题： Re: [postgis-users] spatial query don't use index! 
 

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/9b589e43/attachment.html>