SQL优化问题

Question

在我们的产品数据库中，这是一个运行非常频繁的缓慢sql，正如下面的csv日志显示它通常需要超过1秒，但是当我在数据库服务器中执行sql时，它通常只需要大约0.3秒，这是不可想象的。从计划中，我们可以看到它使用了一个正确的索引。任何人都可以解释它，并有一些优化建议，谢谢！

--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)'",,,,,,,,""

有大量的持续时间日志，我只是粘贴一行。""*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))

Answer 1

在执行检查点时，有可能有较慢的查询。

尝试记录检查点，如果是这样的话，请调优检查点的延迟时间。一般来说，这可以减少每秒写入的字节数。但是会改善缓慢的查询。