Postgis Gist Index

[Paul Ramsey]

This is just tickling the end of my cerebral cortex -- I am sure I  
have seen something like this before.  If you make your box smaller  
and smaller, does the index kick in eventually in mapserver?

P

On 18-Apr-06, at 3:11 PM, Bertsch wrote:

> 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.