sql语句速度慢，有什么办法优化吗？

Question

我们的应用程序有一个非常慢的语句，需要超过11秒，所以我想知道有什么方法可以优化它吗？

SQL语句

SELECT id FROM mapfriends.cell_forum_topic WHERE id in   (
SELECT topicid FROM mapfriends.cell_forum_item WHERE skyid=103230293 GROUP BY topicid ) 
AND categoryid=29 AND hidden=false   ORDER BY restoretime DESC LIMIT 10 OFFSET 0;

   id    
---------
 2471959
 2382296
 1535967
 2432006
 2367281
 2159706
 1501759
 1549304
 2179763
 1598043
(10 rows)

Time: 11444.976 ms

计划

friends=> explain SELECT id FROM friends.cell_forum_topic WHERE id in   (
friends(> SELECT topicid FROM friends.cell_forum_item WHERE skyid=103230293 GROUP BY topicid) 
friends-> AND categoryid=29 AND hidden=false   ORDER BY restoretime DESC LIMIT 10 OFFSET 0;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1443.15..1443.15 rows=2 width=12)
   ->  Sort  (cost=1443.15..1443.15 rows=2 width=12)
         Sort Key: cell_forum_topic.restoretime
         ->  Nested Loop  (cost=1434.28..1443.14 rows=2 width=12)
               ->  HashAggregate  (cost=1434.28..1434.30 rows=2 width=4)
                     ->  Index Scan using cell_forum_item_idx_skyid on cell_forum_item  (cost=0.00..1430.49 rows=1516 width=4)
                           Index Cond: (skyid = 103230293)
               ->  Index Scan using cell_forum_topic_pkey on cell_forum_topic  (cost=0.00..4.40 rows=1 width=12)
                     Index Cond: (cell_forum_topic.id = cell_forum_item.topicid)
                     Filter: ((NOT cell_forum_topic.hidden) AND (cell_forum_topic.categoryid = 29))
(10 rows)

Time: 1.109 ms

索引

friends=> \d cell_forum_item
                                   Table "friends.cell_forum_item"
 Column  |              Type              |                          Modifiers                           
---------+--------------------------------+--------------------------------------------------------------
 id      | integer                        | not null default nextval('cell_forum_item_id_seq'::regclass)
 topicid | integer                        | not null
 skyid   | integer                        | not null
 content | character varying(200)         | 
 addtime | timestamp(0) without time zone | default now()
 ischeck | boolean                        | 
Indexes:
    "cell_forum_item_pkey" PRIMARY KEY, btree (id)
    "cell_forum_item_idx" btree (topicid, skyid)
    "cell_forum_item_idx_1" btree (topicid, id)
    "cell_forum_item_idx_skyid" btree (skyid)
friends=> \d cell_forum_topic
                                                 Table "friends.cell_forum_topic"
   Column    |              Type              |                                      Modifiers                                      

-------------+--------------------------------+-------------------------------------------------------------------------------------
-
 id          | integer                        | not null default nextval(('"friends"."cell_forum_topic_id_seq"'::text)::regclass)
 categoryid  | integer                        | not null
 topic       | character varying              | not null
 content     | character varying              | not null
 skyid       | integer                        | not null
 addtime     | timestamp(0) without time zone | default now()
 reference   | integer                        | default 0
 restore     | integer                        | default 0
 restoretime | timestamp(0) without time zone | default now()
 locked      | boolean                        | default false
 settop      | boolean                        | default false
 hidden      | boolean                        | default false
 feature     | boolean                        | default false
 picid       | integer                        | default 29249
 managerid   | integer                        | 
 imageid     | integer                        | default 0
 pass        | boolean                        | default false
 ischeck     | boolean                        | 
Indexes:
    "cell_forum_topic_pkey" PRIMARY KEY, btree (id)
    "idx_cell_forum_topic_1" btree (categoryid, settop, hidden, restoretime, skyid)
    "idx_cell_forum_topic_2" btree (categoryid, hidden, restoretime, skyid)
    "idx_cell_forum_topic_3" btree (categoryid, hidden, restoretime)
    "idx_cell_forum_topic_4" btree (categoryid, hidden, restore)
    "idx_cell_forum_topic_5" btree (categoryid, hidden, restoretime, feature)
    "idx_cell_forum_topic_6" btree (categoryid, settop, hidden, restoretime)

解释分析

mapfriends=> explain analyze SELECT id FROM mapfriends.cell_forum_topic 
mapfriends->   join (SELECT topicid FROM mapfriends.cell_forum_item WHERE     skyid=103230293 GROUP BY topicid) as tmp
mapfriends->  on mapfriends.cell_forum_topic.id=tmp.topicid
mapfriends->   where categoryid=29 AND hidden=false ORDER BY restoretime  DESC   LIMIT 10 OFFSET 0;
                                                                                    QUERY PLAN                                      

------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------
 Limit  (cost=1446.89..1446.90 rows=2 width=12) (actual time=18016.006..18016.013 rows=10 loops=1)
   ->  Sort  (cost=1446.89..1446.90 rows=2 width=12) (actual time=18016.001..18016.002 rows=10 loops=1)
         Sort Key: cell_forum_topic.restoretime
         Sort Method:  quicksort  Memory: 25kB
         ->  Nested Loop  (cost=1438.02..1446.88 rows=2 width=12) (actual time=16988.492..18015.869 rows=20 loops=1)
               ->  HashAggregate  (cost=1438.02..1438.04 rows=2 width=4) (actual time=15446.735..15447.243 rows=610 loops=1)
                     ->  Index Scan using cell_forum_item_idx_skyid on cell_forum_item  (cost=0.00..1434.22 rows=1520 width=4) (actual time=302.378..15429.782 rows=7133 loops=1)
                           Index Cond: (skyid = 103230293)
               ->  Index Scan using cell_forum_topic_pkey on cell_forum_topic  (cost=0.00..4.40 rows=1 width=12) (actual time=4.210..4.210 rows=0 loops=610)
                     Index Cond: (cell_forum_topic.id = cell_forum_item.topicid)
                     Filter: ((NOT cell_forum_topic.hidden) AND (cell_forum_topic.categoryid = 29))
 Total runtime: 18019.461 ms

Answer 1

您能给我们提供一些有关表格（统计数据）和配置的更多信息吗？

SELECT version();
SELECT category, name, setting FROM pg_settings WHERE name IN('effective_cache_size', 'enable_seqscan', 'shared_buffers');
SELECT * FROM pg_stat_user_tables WHERE relname IN('cell_forum_topic', 'cell_forum_item');
SELECT * FROM pg_stat_user_indexes WHERE relname IN('cell_forum_topic', 'cell_forum_item');
SELECT * FROM pg_stats WHERE tablename IN('cell_forum_topic', 'cell_forum_item');

在获取此数据之前，请使用 ANALYZE。

看来您的索引有问题，这是所有查询花费所有时间的地方：

->使用 cell_forum_item_idx_skyid 进行索引扫描
cell_forum_item（成本=0.00..1434.22
行=1520 宽度=4）（实际
时间=302.378..15429.782行=7133
循环=1）

如果您定期使用 VACUUM FULL（不推荐！），索引膨胀可能是您的问题。 REINDEX 可能是一个好主意，只是为了确定：

REINDEX TABLE cell_forum_item;

谈到索引，您可以删除其中的几个，这些已经过时了：

"idx_cell_forum_topic_6" btree (categoryid, settop, hidden, restoretime)
"idx_cell_forum_topic_3" btree (categoryid, hidden, restoretime)

其他索引具有相同的数据，也可以被数据库使用。

---

您似乎遇到了几个问题：

• 自动清理功能已关闭或已关闭
  在后面。最后一次自动清理已开启
  2010-12-02 你有 256734 人死亡
  一张表中有 451430 个元组已死亡
  另一个中的......你必须做
  关于这个，这是一个
  严重的问题。
• 当 autovacuum 再次工作时，您
  必须进行 VACUUM FULL 和
  REINDEX 强制表重写并
  摆脱你的所有空白空间
  表。
• 解决真空问题后，您
  还必须分析：数据库
  预计有 1520 个结果，但实际得到 7133 个
  结果。这可能是一个问题
  统计数据，也许你必须
  增加统计。
• 查询本身需要一些重写
  还有：它得到 7133 个结果，但它
  只需要 610 个结果。 90%以上
  结果丢失了......并且得到
  这7133需要很多时间，结束了
  15秒。通过使用不带 GROUP BY 的 JOIN 或使用 EXISTS （也不带 GROUP BY）来摆脱子查询。

但在遇到新问题或其他问题之前，首先要让 autovacuum 回到正轨。

Answer 2

该问题不是由于缺乏查询计划的缓存而导致，而是很可能是由于缺乏适当的索引而选择了计划