Thanks for your reply
I was also using this statement before, but with the same results.
The PG Log
BEGIN
DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text from
south_america_large WHERE the_geom && setSRID('BOX3D(-54 -20.125,-39
-8.875)'::BOX3D, -1 )
FETCH ALL in mycursor
then running the analyse:
**********************************************************************
*********************************
geo_base_index=# BEGIN;
BEGIN
geo_base_index=# EXPLAIN ANALYZE
geo_base_index-# DECLARE mycursor BINARY CURSOR FOR SELECT
asbinary(force_collec
tion(force_2d(the_geom)),'NDR'),gid::text from south_america_large
WHERE
the_geo
m && setSRID('BOX3D(-54 -20.125,-39 -8.875)'::BOX3D, -1 );
QUERY PLAN
----------------------------------------------------------------------
----------
----------------------------------------------------------------------
----------
----------------------------------------------------------------
Seq Scan on south_america_large (cost=0.00..2393.04 rows=2554
width=1973)
Filter: (the_geom &&
'010300000001000000050000000000000000004BC00000000000203
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C0000
0000000804
3C000000000002034C00000000000004BC000000000002034C0'::geometry)
(2 rows)
so i was running a second analyse without the declaration of
mycurser and it
was using the index
geo_base_index=# BEGIN;
BEGIN
geo_base_index=# EXPLAIN ANALYZE
geo_base_index-# SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid
::text from south_america_large WHERE the_geom && setSRID('BOX3D(-54
-20.125,-39
-8.875)'::BOX3D, -1 );
QUERY PLAN
----------------------------------------------------------------------
----------
----------------------------------------------------------------------
----------
----------------------------------------------------------------------
----
Bitmap Heap Scan on south_america_large (cost=24.94..2302.79
rows=2554
width=1
973) (actual time=88.130..174.118 rows=2485 loops=1)
Filter: (the_geom &&
'010300000001000000050000000000000000004BC00000000000203
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C0000
0000000804
3C000000000002034C00000000000004BC000000000002034C0'::geometry)
-> Bitmap Index Scan on south_america_large_index
(cost=0.00..24.94
rows=25
54 width=0) (actual time=86.303..86.303 rows=2485 loops=1)
Index Cond: (the_geom &&
'010300000001000000050000000000000000004BC0000
00000002034C00000000000004BC00000000000C021C000000000008043C0000000000
0C021C0000
00000008043C000000000002034C00000000000004BC000000000002034C0'::geomet
ry)
Total runtime: 184.970 ms
(5 rows)
in comparision to the database without the index key
geo_base=# EXPLAIN ANALYZE
geo_base-# SELECT
asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from south_america_large WHERE the_geom && setSRID('BOX3D(-54
-20.125,-39
-8.87
5)'::BOX3D, -1 );
QUERY PLAN
----------------------------------------------------------------------
----------
----------------------------------------------------------------------
----------
----------------------------------------------------------------
Seq Scan on south_america_large (cost=0.00..2396.85 rows=2535
width=2054)
(act
ual time=233.041..364.162 rows=2485 loops=1)
Filter: (the_geom &&
'010300000001000000050000000000000000004BC00000000000203
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C0000
0000000804
3C000000000002034C00000000000004BC000000000002034C0'::geometry)
Total runtime: 367.541 ms
(3 rows)
So far, now i am a little bit confused how to create a statement
not using
the declaration of mycurser?
Thanks,
Clemens
--
View this message in context: http://www.nabble.com/Postgis-Gist-
Index-t1469264.html#a3977908
Sent from the Mapserver - User forum at Nabble.com.
