postgresql - Postgres 9.6 函数与直接 sql 相比表现不佳

Question

我有这个功能，它可以工作，它给出了最近的 b 记录。

create or replace function most_recent_b(the_a a) returns b as $$
    select distinct on (c.a_id) b.*
    from c 
    join b on b.c_id = c.id
    where c.a_id = the_a.id 
    order by c.a_id, b.date desc
$$ language sql stable;

这对真实数据运行约 5000 毫秒。 V.S.以下运行时间为 500 毫秒

create or replace function most_recent_b(the_a a) returns b as $$
    select distinct on (c.a_id) b.*
    from c 
    join b on b.c_id = c.id
    where c.a_id = 1347 
    order by c.a_id, b.date desc
$$ language sql stable;

唯一的区别是我用值 1347 对 a.id 进行了硬编码，而不是使用它的参数值。

同样在没有函数的情况下运行这个查询也给我大约 500 毫秒的速度

我运行的是 PostgreSQL 9.6，所以我在其他地方看到的建议的查询规划器功能失败结果不应该适用于我，对吗？

我确定问题不是查询本身，因为这是我的第三次迭代，获得此结果的不同技术都会导致在函数内部时同样变慢。

应@laurenz-albe 的要求

带有常量的 EXPLAIN (ANALYZE, BUFFERS) 的结果

Unique  (cost=60.88..60.89 rows=3 width=463) (actual time=520.117..520.122 rows=1 loops=1)
  Buffers: shared hit=14555
  ->  Sort  (cost=60.88..60.89 rows=3 width=463) (actual time=520.116..520.120 rows=9 loops=1)
        Sort Key: b.date DESC
        Sort Method: quicksort  Memory: 28kB
        Buffers: shared hit=14555
        ->  Hash Join  (cost=13.71..60.86 rows=3 width=463) (actual time=386.848..520.083 rows=9 loops=1)
              Hash Cond: (b.c_id = c.id)
              Buffers: shared hit=14555
              ->  Seq Scan on b (cost=0.00..46.38 rows=54 width=459) (actual time=25.362..519.140 rows=51 loops=1)
                    Filter: b_can_view(b.*)
                    Rows Removed by Filter: 112
                    Buffers: shared hit=14530
              ->  Hash  (cost=13.67..13.67 rows=3 width=8) (actual time=0.880..0.880 rows=10 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
                    Buffers: shared hit=25
                    ->  Subquery Scan on c  (cost=4.21..13.67 rows=3 width=8) (actual time=0.222..0.872 rows=10 loops=1)
                          Buffers: shared hit=25
                          ->  Bitmap Heap Scan on c c_1  (cost=4.21..13.64 rows=3 width=2276) (actual time=0.221..0.863 rows=10 loops=1)
                                Recheck Cond: (a_id = 1347)
                                Filter: c_can_view(c_1.*)
                                Heap Blocks: exact=4
                                Buffers: shared hit=25
                                ->  Bitmap Index Scan on c_a_id_c_number_idx  (cost=0.00..4.20 rows=8 width=0) (actual time=0.007..0.007 rows=10 loops=1)
                                      Index Cond: (a_id = 1347)
                                      Buffers: shared hit=1
Execution time: 520.256 ms

这是在传递参数的情况下运行六次后的结果(正好是你预测的六次:))
慢查询；

Unique  (cost=57.07..57.07 rows=1 width=463) (actual time=5040.237..5040.243 rows=1 loops=1)
  Buffers: shared hit=145325
  ->  Sort  (cost=57.07..57.07 rows=1 width=463) (actual time=5040.237..5040.240 rows=9 loops=1)
        Sort Key: b.date DESC
        Sort Method: quicksort  Memory: 28kB
        Buffers: shared hit=145325
        ->  Nested Loop  (cost=0.14..57.06 rows=1 width=463) (actual time=912.354..5040.195 rows=9 loops=1)
              Join Filter: (c.id = b.c_id)
              Rows Removed by Join Filter: 501
              Buffers: shared hit=145325
              ->  Index Scan using c_a_id_idx on c (cost=0.14..9.45 rows=1 width=2276) (actual time=0.378..1.171 rows=10 loops=1)
                    Index Cond: (a_id = $1)
                    Filter: c_can_view(c.*)
                    Buffers: shared hit=25
              ->  Seq Scan on b (cost=0.00..46.38 rows=54 width=459) (actual time=24.842..503.854 rows=51 loops=10)
                    Filter: b_can_view(b.*)
                    Rows Removed by Filter: 112
                    Buffers: shared hit=145300
Execution time: 5040.375 ms

值得注意的是，我涉及一些严格的行级安全性，我怀疑这就是为什么这些查询都很慢，但是，一个比另一个慢 10 倍。

我已经更改了我原来的表名，希望我的搜索和替换在这里是好的。

Answer 1

查询执行的昂贵部分是过滤器 b_can_view(b.*)，它必须来自您的行级安全定义。

快速执行:

Seq Scan on b (cost=0.00..46.38 rows=54 width=459)
              (actual time=25.362..519.140 rows=51 loops=1)
  Filter: b_can_view(b.*)
  Rows Removed by Filter: 112
  Buffers: shared hit=14530

执行缓慢:

Seq Scan on b (cost=0.00..46.38 rows=54 width=459)
              (actual time=24.842..503.854 rows=51 loops=10)
  Filter: b_can_view(b.*)
  Rows Removed by Filter: 112
  Buffers: shared hit=145300

区别在于在慢速情况下(loops=10)扫描执行了 10 次并且接触了 10 倍多的数据 block 。

当使用通用计划时，PostgreSQL 低估了 c 中有多少行将满足条件 c.a_id = $1，因为它不知道实际值为 1347，高于平均值。

由于 PostgreSQL 认为从 c 中最多只有一行，因此它选择了一个嵌套循环连接，并在内侧对 b 进行顺序扫描。

现在两个问题结合起来:

1. 调用函数 b_can_view 每行花费超过 3 毫秒(PostgreSQL 不知道)，这占 163 行的顺序扫描花费的半秒。

2. 在 c 中实际找到了 10 行，而不是预测的 1 行，因此表 b 被扫描了 10 次，最后得到一个查询持续时间5 秒。

那你能做什么呢？

• 告诉 PostgreSQL b_can_view 有多昂贵。使用 ALTER TABLE 将该函数的 COST 设置为 1000 或 10000 以反射(reflect)实际情况。仅凭这一点还不足以获得更快的计划，因为 PostgreSQL 认为它无论如何都必须执行单个顺序扫描，但为优化器提供正确的数据是一件好事。

• 在 b(c_id) 上创建索引。这将使 PostgreSQL 避免对 b 进行顺序扫描，一旦它意识到该函数的开销有多大，它就会尝试这样做。

此外，尝试使函数 b_can_view 更便宜。这将使您的体验变得更好。