spatial query don't use index!
|
View:
New views
6 Messages
—
Rating Filter:
Alert me
|
 |
spatial query don't use index!
by yishh
::
Rate this Message:
|
|
|
Re: spatial query don't use index!
by Suhr, Ralf
::
Rate this Message:
|
|
|
Re: spatial query don't use index!
by Guillaume Lelarge-3
::
Rate this Message:
|
|
|
Re: spatial query don't use index!
by yishh
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
Re: spatial query don't use index!
by Paragon Corporation-2
::
Rate this Message:
Some parts of this message have been removed.
Learn more about Nabble's security policy.
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
http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
|
|
Re: spatial query don't use index!
by yishh
::
Rate this Message:
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
http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@... http://postgis.refractions.net/mailman/listinfo/postgis-users |
| Free embeddable forum powered by Nabble | Forum Help |
