[postgis-users] question on gist performance

[Kevin Neufeld]

Stefan Zweig wrote:
>> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE the_geom && 
>> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);
>>     
>
> "Index Scan using mytable_the_geom_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=100.198..247.711 rows=4 loops=1)"
> "  Index Cond: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
> "  Filter: (the_geom && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
> "Total runtime: 247.782 ms"
>
>   
>> EXPLAIN ANALYZE SELECT gid FROM mytable WHERE bbox && 
>> SetSrid('BOX(13.760675 51.171104,15.231802 51.83737)'::box2d,4326);
>>     
>
> "Index Scan using mytable_bbox_gist on mytable  (cost=0.00..4.95 rows=1 width=4) (actual time=0.020..0.078 rows=4 loops=1)"
> "  Index Cond: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
> "  Filter: (bbox && '0103000020E610000001000000050000000000004077852B40000000C0E69549400000004077852B40000000002FEB4940000000C0AE762E40000000002FEB4940000000C0AE762E40000000C0E69549400000004077852B40000000C0E6954940'::geometry)"
>
> "Total runtime: 0.137 ms"
Are these averaged times, ie after running the explain analyze 3 or 4 
times?  Keep in mind that the first time you run the query, it may or 
may not be cached. The best way to get times is to:
1. restart your server (to clear all caches)
2. run explain analyze on your first query 3 or 4 times, taking an average.
3. restart your server (to again clear the cache so that it doesn't 
affect runtimes in the second query)
4. run explain analyze on the second query 3 or 4 times, again taking an 
average.

-- Kevin

-------------
Kevin Neufeld
Software Developer
Refractions Research Inc.
300-1207 Douglas St.
Victoria, B.C., V8W 2E7

Phone: (250) 383-3022
Email: kneufeld at refractions.net