[postgis-users] Poor performance

[David Blasby]

David Mitchell wrote:

  >                                                             QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------- 
> 
>  Index Scan using links_gist_majhwys2 on links  (cost=0.00..6.04 rows=1 
> width=240) (actual time=0.060..731.866 rows=23535 loops=1)
>    Index Cond: (the_geom && 'SRID=-1;BOX3D(-73.8255319148936 40 
> 0,-69.1744680851064 44 0)'::geometry)
>    Filter: ((((ascii((flag)::text) / 10))::text = '1'::text) OR 
> (((ascii((flag)::text) / 10))::text = '2'::text))
>  Total runtime: 1350.587 ms
> 
> This is only returning 23 thousand rows, so it's got me why it takes so 
> long. Any help would be appreciated.

This says that its only taking 0.7 seconds - thats pretty good for 
pulling 23,000 random rows off disk.

Your "func_class(flag)" looks like its taking the rest of the 0.7 seconds.

Its only taking about 0.05 ms (!) to pull each row...

You could try re-building the index:
1. drop your index
2. vacuum analyse the table
3. re-build the index
4. vacuum analyse the table

You could also try re-writing your func_class() function.

The next step would be to get faster drives or RAID them - whats the CPU 
doing during the query (if its not high, you're IO bound)?

dave