[postgis-users] PERFORMANCE PROBLEMS II (More Data)

[David Blasby]

Alvaro Zabala wrote:

> 
> My Java application is a JUMP plugin.
> Refraction's plugin es good, but it works downloading all features from 
> database, to work with them in memory.
> We are working with massive datasets, and we need to execute one query 
> by rectangle any time.
> (When changing zoom level, plugin execute a query by rectangle)
> 
> I've made a query explain -to see if the query is using spatial index) 
> and I get:
> "Index Scan using spt_index_muni on municipios  (cost=0.00..5.95 rows=1 
> width=86)"
> It uses spatial index.
> May I improve the query execution time? How?
> My table only has 700 rows.

If your table only has 700 rows, it should be very quick.  We do the 
same thing on a table with 17,000,000 rows and the response time is <0.5 
seconds for a medium-sized area (about 1,000 lines selected).

Doing this on a 700 row table should be instant - unless your individual 
geometries are huge (>100,000 points).

If you do an

EXPLAIN ANALYSE <normal query>;

postgresql will give you execution time details as well as the plan.

The only reason your query would be slow is if you either have a 
hopelessly tiny amount of memory or you machine is swamped doing other 
stuff.

dave