SQL optimization problem

In our product database, ther is a slow sql which runs very frequently , just as the following csv logs shows that it usually taks more than 1 second , But it is so inconceivable that when I excute the sql in Database Server, it usually just taks about 0.3 s。From the plan ,we can s开发者_如何学Cee that it uses a right index。 Anybody can explain it and have some optimization advice,thanks !

--sql
SELECT id, content, the_geo, lon, lat, skyid, addtime
  FROM skytf.tbl_map
 where skytf.ST_Distance_sphere(the_geo, skytf.geometryFromText('POINT(0 -4.0E-5)')) <= 1000
 and the_geo && skytf.ST_BUFFER(skytf.geometryfromtext('POINT(0 -4.0E-5)'),0.005)  
 order by addtime desc limit 30



--csv log 
2011-08-30 03:02:06.029 CST,"lbs","skytf",28656,"192.168.168.46:53430",4e5b9d45.6ff0,356,"SELECT",2011-08-29 22:08:05 CST,106/3030952,0,LOG,00000,"duration: 1782.945 ms  execute <unnamed>:                            SELECT id,content,the_geo,lon,lat,skyid,addtime FROM skytf.tbl_map where          skytf.ST_Distance_sphere(the_geo,skytf.geometryFromText($1))<=1000          and the_geo && skytf.ST_BUFFER(skytf.geometryfromtext($2),0.005)      order by addtime desc limit 30          ","parameters: $1 = 'POINT(0 -4.0E-5)', $2 = 'POINT(0 -4.0E-5)'",,,,,,,,""

There are plenty of the duration logs, I just paste a line 。 ""*duration: 1782.945 ms"*

--explain analyze 
skytf=> explain analyze SELECT id,content,the_geo,lon,lat,skyid,addtime FROM skytf.tbl_map 
skytf-> where          skytf.ST_Distance_sphere(the_geo,skytf.geometryFromText('POINT(0 -4.0E-5)'))<=1000          
skytf-> and the_geo && skytf.ST_BUFFER(skytf.geometryfromtext('POINT(0 -4.0E-5)'),0.005)      
skytf-> order by addtime desc limit 30     ;
                                                                         QUERY PLAN                                                 

-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=11118.56..11118.64 rows=30 width=128) (actual time=338.031..338.037 rows=30 loops=1)
   ->  Sort  (cost=11118.56..11124.60 rows=2416 width=128) (actual time=338.030..338.032 rows=30 loops=1)
         Sort Key: addtime
         Sort Method:  top-N heapsort  Memory: 29kB
         ->  Bitmap Heap Scan on tbl_map  (cost=201.51..11047.21 rows=2416 width=128) (actual time=53.455..309.962 rows=78121 loops=1)
               Recheck Cond: ((the_geo)::box && '(0.005,0.00496),(-0.005,-0.00504)'::box)
               Filter: (skytf.st_distance_sphere(the_geo, '01010000000000000000000000F168E388B5F804BF'::skytf.geometry) <=1000::double precision)
               ->  Bitmap Index Scan on tbl_map_idx_gin  (cost=0.00..200.91 rows=7249 width=0) (actual time=49.392..49.392 rows=78559 loops=1)
                     Index Cond: ((the_geo)::box && '(0.005,0.00496),(-0.005,-0.00504)'::box)
 Total runtime: 338.125 ms
(10 rows)


--table information
skytf=> \dt+  skytf.tbl_map
                         List of relations
   Schema   |     Name      | Type  | Owner |  Size  | Description 
------------+---------------+-------+-------+--------+-------------
 skytf | tbl_map | table | lbs   | 158 MB | 
(1 row)

skytf=> \d  skytf.tbl_map
                                           Table "skytf.tbl_map"
    Column    |              Type              |                               Modifiers                               
--------------+--------------------------------+-----------------------------------------------------------------------
 id           | integer                        | not null default nextval('skytf.tbl_map_id_seq'::regclass)
 content      | character varying(100)         | 
 lon          | double precision               | 
 lat          | double precision               | 
 skyid        | integer                        | 
 addtime      | timestamp(1) without time zone | default now()
 the_geo      | skytf.geometry            | 
 viewcount    | integer                        | default 0
 lastreadtime | timestamp without time zone    | 
 ischeck      | boolean                        | 
Indexes:
    "tbl_map_pkey" PRIMARY KEY, btree (id)
    "idx_map_book_skyid" btree (skyid, addtime)
    "tbl_map_idx_gin" gist ((the_geo::box))

---

It is possible to have slower queries while checkpoint is executed.

Try to log checkpoints and if this is your case tune checkpoints for latency. In general this may reduce bytes written per sec. but will improve slow queries.