Postgresql在不同服务器上的不同查询计划

Question

摘要：在Postgres 9.3.15上，我的开发和生产计算机上的相同查询与生产计算机的查询计划相差300倍！

我意识到“限制”和“偏移量”在Postgresql中并不是很好，但这并不能解释为什么它在我的开发中速度很快并且生产速度很慢。

有什么建议吗？我试着改变cpu_tuple_cost（0.1〜0.5 - 没有帮助）

---

我的生产服务器（天青：4个CPU，RAM 16gig）需要1100毫秒来运行此查询：

prod=# SELECT "designs".* FROM "designs" WHERE "designs"."user_id" IN (SELECT "users"."id" FROM "users" WHERE (code_id=393)) ORDER BY updated_at desc, "designs"."updated_at" DESC LIMIT 20 OFFSET 0; 
Time: 1175.486 ms 

同时我dev服务器（Virtualbox，laptop，2 gig ram）需要4ms才能在同一个数据库上运行相同的查询。

dev=# SELECT "designs".* FROM "designs" WHERE "designs"."user_id" IN (SELECT "users"."id" FROM "users" WHERE (code_id=393)) ORDER BY updated_at desc, "designs"."updated_at" DESC LIMIT 20 OFFSET 0; 
Time: 4.249 ms 

生产的查询计划是这样的：

prod=# explain SELECT "designs".* FROM "designs" WHERE "designs"."user_id" IN (SELECT "users"."id" FROM "users" WHERE (code_id=393)) ORDER BY updated_at desc, "designs"."updated_at" DESC LIMIT 20 OFFSET 0; 
                  QUERY PLAN 
------------------------------------------------------------------------------------------------------------------------------- 
Limit (cost=169.00..113691.20 rows=20 width=966) 
    -> Nested Loop Semi Join (cost=169.00..51045428.02 rows=8993 width=966) 
     -> Index Scan Backward using design_modification_date_idx on designs (cost=85.00..1510927.32 rows=538151 width=966) 
     -> Index Scan using "User_UserUID_key" on users (cost=84.00..92.05 rows=1 width=4) 
       Index Cond: (id = designs.user_id) 
       Filter: (code_id = 393) 
(6 rows) 

Time: 1.165 ms 

dev的查询计划是这样的：

dev=# explain SELECT "designs".* FROM "designs" WHERE "designs"."user_id" IN (SELECT "users"."id" FROM "users" WHERE (code_id=393)) ORDER BY updated_at desc, "designs"."updated_at" DESC LIMIT 20 OFFSET 0; 
               QUERY PLAN 
----------------------------------------------------------------------------------------------------------- 
Limit (cost=5686.78..5686.83 rows=20 width=964) 
    -> Sort (cost=5686.78..5689.41 rows=1052 width=964) 
     Sort Key: designs.updated_at 
     -> Nested Loop (cost=0.71..5658.79 rows=1052 width=964) 
       -> Index Scan using code_idx on users (cost=0.29..192.63 rows=67 width=4) 
        Index Cond: (code_id = 393) 
       -> Index Scan using "Design_idx_owneruid" on designs (cost=0.42..73.58 rows=16 width=964) 
        Index Cond: (user_id = users.id) 
(8 rows) 

Time: 0.736 ms 

---

编辑：确定倾销生产数据的全新副本后，我发现查询规划器是相同的（所以这是一个数据问题 - 对不起！）。查询仍然很慢，但有什么想法可以做些改进呢？我已经尝试添加上设计（的updated_at，USER_ID）和用户（ID，code_id）指标无济于事

输出解释（分析一下，缓冲区）：

---

Limit (cost=0.72..10390.79 rows=20 width=962) (actual time=1485.810..22025.828 rows=20 loops=1) 
    Buffers: shared hit=883264 read=164340 
    -> Nested Loop Semi Join (cost=0.72..4928529.42 rows=9487 width=962) (actual time=1485.809..22025.809 rows=20 loops=1) 
     Buffers: shared hit=883264 read=164340 
     -> Index Scan Backward using design_modification_date_idx on designs (cost=0.42..1442771.50 rows=538270 width=962) (actual time=1.737..18444.598 rows=263043 loops=1) 
       Buffers: shared hit=108266 read=149409 
     -> Index Scan using "User_UserUID_key" on users (cost=0.29..6.48 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=263043) 
       Index Cond: (id = designs.user_id) 
       Filter: (code_id = 393) 
       Rows Removed by Filter: 1 
       Buffers: shared hit=774998 read=14931 
Total runtime: 22027.477 ms 
(12 rows) 

---

编辑：对建议查询的附加说明

dev=# explain (analyze) SELECT designs.* 
FROM designs 
    JOIN (SELECT * 
      FROM users 
      WHERE code_id=393 
      OFFSET 0 
     ) users 
     ON designs.user_id = users.id 
ORDER BY updated_at desc 
LIMIT 20; 


Limit (cost=0.72..13326.65 rows=20 width=962) (actual time=2597.877..95734.152 rows=20 loops=1) 
    -> Nested Loop (cost=0.72..6321154.70 rows=9487 width=962) (actual time=2597.877..95734.135 rows=20 loops=1) 
     Join Filter: (designs.user_id = users.id) 
     Rows Removed by Join Filter: 143621402 
     -> Index Scan Backward using design_modification_date_idx on designs (cost=0.42..1410571.52 rows=538270 width=962) (actual time=0.024..5217.228 rows=263043 loops=1) 
     -> Materialize (cost=0.29..1562.31 rows=608 width=4) (actual time=0.000..0.146 rows=546 loops=263043) 
       -> Subquery Scan on users (cost=0.29..1559.27 rows=608 width=4) (actual time=0.021..1.516 rows=546 loops=1) 
        -> Index Scan using code_idx on users users_1 (cost=0.29..1553.19 rows=608 width=602) (actual time=0.020..1.252 rows=546 loops=1) 
          Index Cond: (code_id = 393) 
Total runtime: 95734.353 ms 
(10 rows) 

Answer 1

这里是我如何阅读此内容。 ANALYSE和BUFFERS可能会有帮助，但我不这么认为。

在你的开发数据库，​​它期望找到67个用户，因此它首先选择这些，然后排序然后做一个限制和一个偏移量。而对于数据量来看，这很快。

在生产上，它假定每个用户有一个用户并且倒退，但是每个用户的设计数量要大得多，因此它首先沿着排序标准搜索设计，并过滤用户。当你发现它在找到20行后可以停下来时，这是有道理的。但是数据统计数据使这个计划成为一个糟糕的计划，你会得到一些检查一堆额外记录的东西来找到相关的记录。

所以这是我猜测发生了什么。请确保你明白为什么在你尝试修复之前.....

现在，如果你要在用户表上创建一个(user_id, code_id)索引，你可能会得到显着的加速，因为你可以避免在索引扫描阶段。

另一个选项可能是在设计表上创建一个(modification_date, user_id)的索引。然而，这对我来说似乎是一个更长的镜头。

Answer 2

的问题是，与code_id = 393的users大多与designs低updated_at，这样的PostgreSQL HAST从designs扫描263043行它已发现有20满足条件之前。

由于PostgreSQL没有交叉表统计信息，它不知道它的想法是通过使用适当的索引来避免排序导致它比预期的少数扫描行更多。

你可以重写查询的使用又老又丑招用OFFSET 0，不更改查询的语义，但防止PostgreSQL的从考虑可疑优化：

SELECT designs.* 
FROM designs 
    JOIN (SELECT * 
      FROM users 
      WHERE code_id=393 
      OFFSET 0 /* avoid optimizations beyond using an index for code_id */ 
     ) u 
     ON designs.user_id = users.id 
ORDER BY updated_at desc 
LIMIT 20; 

这应该给你想要的快计划。

如果这还不足以推动PostgreSQL选择好计划，那么可以通过删除design_modification_date_idx索引来进一步帮助它，如果这是一个选项。