Postgis Gist Index

[Bertsch]

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
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C00000000000804
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
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C00000000000804
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
00000002034C00000000000004BC00000000000C021C000000000008043C00000000000C021C0000
00000008043C000000000002034C00000000000004BC000000000002034C0'::geometry)
 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
4C00000000000004BC00000000000C021C000000000008043C00000000000C021C00000000000804
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.