« Return to Thread: spatial query don't use index!

Re: spatial query don't use index!

by yishh :: Rate this Message:

Reply to Author | View in Thread

Some parts of this message have been removed. Learn more about Nabble's security policy.

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@... [mailto:postgis-users-bounces@...] 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@...

http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

« Return to Thread: spatial query don't use index!