奇异优化效果与施工

Question

我有四张桌子，让我们把它们命名为：

1. 表A，15M行
2. 表B，40K行，
3. 表C，30K行，
4. 表D，25M行

(kk -指百万)

我有一个遗留查询，它是这样构造的：

select C.<some_fields>,B.<some_fields>,D.<some_fields> from C
inner join A on C.x = A.x
inner join D on D.z = 123 and D.a_id = A.a_id
inner join B on C.x = B.x and B.z = 123
where A.type = 'Xxx'

这个查询非常慢，执行结果需要3分钟(对于特殊情况，它返回35k行)。

但是，当我将其更改为以下结构时：

with t as (
   select C.<some_fields>,D.<some_fields> from C
   inner join A on C.x = A.x
   inner join D on D.z = 123 and D.a_id = A.a_id
   where A.type = 'Xxx'
)
select t.*, B.<some_fields>,
inner join B on t.x = B.x and B.z = 123

它开始工作的速度快了30倍(也就是说，现在检索相同的结果需要最多6秒)。

让我们假设，索引的构造是正确的。当我注意到这个块(我已经打包到with ( ... )中)工作得非常快(并且返回与整个查询非常相似的数据量)时，我就产生了这样的想法。

所以我的问题是:原因是什么？为什么Postgres不能在内部建立适当的计划或者做同样的事情？

更新

旧查询的执行计划：

Nested Loop  (cost=1.83..1672.82 rows=1 width=54) (actual time=8.178..91515.625 rows=37373 loops=1)
  ->  Nested Loop  (cost=1.42..1671.47 rows=1 width=62) (actual time=8.108..90883.567 rows=37373 loops=1)
        Join Filter: (a.x = b.x)
        Rows Removed by Join Filter: 9132436
        ->  Index Scan using b_pkey on B b  (cost=0.41..8.43 rows=1 width=71) (actual time=0.022..0.782 rows=241 loops=1)
              Index Cond: (z = 123)
        ->  Nested Loop  (cost=1.00..1660.48 rows=146 width=149) (actual time=0.027..363.227 rows=38049 loops=241)
              ->  Index Only Scan using idx_1 on D d  (cost=0.56..424.59 rows=146 width=8) (actual time=0.017..50.869 rows=64176 loops=241)
                    Index Cond: (z = 123)
                    Heap Fetches: 15564503
              ->  Index Scan using a_pkey on A a  (cost=0.44..8.46 rows=1 width=149) (actual time=0.003..0.004 rows=1 loops=15466416)
                    Index Cond: (a_id = d.a_id)
  ->  Index Scan using c_pkey on C c (cost=0.41..1.08 rows=1 width=8) (actual time=0.005..0.007 rows=1 loops=37373)
        Index Cond: (x = a.x)
        Filter: ((type)::text = 'Xxx')
Planning time: 3.468 ms
Execution time: 91541.019 ms

新查询的执行计划：

Hash Join  (cost=1828.09..1830.28 rows=1 width=94) (actual time=0.654..1130.542 rows=37376 loops=1)
  Hash Cond: (t.x = b.x)
  CTE t
    ->  Nested Loop  (cost=1.42..1819.64 rows=81 width=158) (actual time=0.060..761.058 rows=38052 loops=1)
          ->  Nested Loop  (cost=1.00..1660.48 rows=146 width=149) (actual time=0.039..461.235 rows=38052 loops=1)
                ->  Index Only Scan using idx_1 on D d  (cost=0.56..424.59 rows=146 width=8) (actual time=0.024..73.972 rows=64179 loops=1)
                      Index Cond: (z = 123)
                      Heap Fetches: 64586
                ->  Index Scan using a_pkey on A a  (cost=0.44..8.46 rows=1 width=149) (actual time=0.004..0.004 rows=1 loops=64179)
                      Index Cond: (a_id = d.a_id)
          ->  Index Scan using c_pkey on C c  (cost=0.41..1.07 rows=1 width=17) (actual time=0.004..0.005 rows=1 loops=38052)
                Index Cond: (x = a.x)
                Filter: ((type)::text = 'Xxx')
  ->  CTE Scan on t  (cost=0.00..1.62 rows=81 width=104) (actual time=0.063..854.405 rows=38052 loops=1)
  ->  Hash  (cost=8.43..8.43 rows=1 width=71) (actual time=0.353..0.353 rows=241 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 34kB
        ->  Index Scan using b_pkey on B b  (cost=0.41..8.43 rows=1 width=71) (actual time=0.012..0.262 rows=241 loops=1)
              Index Cond: (z = 123)
Planning time: 1.221 ms
Execution time: 1147.267 ms

更新-2

最近，亲爱的评论员注意到，这个问题是由对行数的错误估计引起的，他们建议我做vacuum analyze。但是我在Amazon-RDS上运行这个服务器，在这里启用了autovacuum功能。此外，我还尝试运行亚马逊文档中建议的用于显示符合真空条件的表的脚本，它向我展示了0张符合真空要求的表。

更新-3:注释中建议的已执行的ANALYZE没有改变计划中的错误行估计，而是提高了查询的“旧”变体的速度。对于我的核心问题，我仍然没有完全理解:为什么第二类查询具有显著的高速度(即使是w/o分析)？

Answer 1

从解释中可以清楚地看到，PostgreSQL产生了错误的估计，并为第一个查询选择了糟糕的计划。表D列z上的等式选择器估计完全错误(大约500次)。

第二次更好，因为有一个规划围栏，克雷格指出。

让我们集中精力进行第一个查询。

由于缺少/旧/不足的统计数据，规划师提出了错误的估计数。

几乎所有的统计数据都可以在pg_stats中看到。您应该检查这个视图，最好在这里粘贴相关的行。

SELECT * FROM pg_stats WHERE tablename='d' and attname='z';

如果一个列有一些奇怪的统计分布(非均匀、非高斯、偏斜、带隐藏模式的伪随机等)，那么分析后的统计模块可能无法捕捉规划者所需的规律。

在许多情况下，使用更大的样本有助于产生真实的估计。有一个配置参数以提高用于分析的样本大小。，可以这样使用：

SET default_statistics_target TO 200;
ANALYZE A;
ANALYZE B;
ANALYZE C;
SET default_statistics_target TO 1000;
ANALYZE D;

尝试使用值，然后重试选择的查询。如果有帮助，可以使用ALTER TABLE D ALTER COLUMN Z SET STATISTICS 1000永久地提高样本大小。

PS。与往常一样，您应该确保内存/资源配置是正确的。“资源配置”部分中的所有设置都应调整为实际的服务器资源(数据库和内存大小、raid数组类型、ssd驱动器)。