sql - 有没有更有效的方法来编写此 SQL？

Question

有没有办法在 Postgres 中更有效地编写它？我将在其他几个查询中重新使用它。表A很大，表B是A的1/3大小，C很小。

SELECT a.field1, b.field2, c.field3
FROM A a
         LEFT JOIN B b on a.ref_id = b.id
         LEFT JOIN C c on b.other_ref_id = c.id
WHERE a.field1 IN (...)

执行计划显示第一个 LEFT JOIN 的 loops 值很大。

解释计划:

Gather  (cost=1002.62..1290550.84 rows=856452 width=74) (actual time=0.495..1554.401 rows=850836 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=4022375 read=234277
  ->  Hash Left Join  (cost=2.62..1203905.64 rows=356855 width=74) (actual time=0.263..1441.760 rows=283612 loops=3)
        Hash Cond: (b.other_ref_id = c.id)
        Buffers: shared hit=4022375 read=234277
        ->  Nested Loop Left Join  (cost=1.13..1202967.39 rows=356855 width=44) (actual time=0.145..1402.434 rows=283612 loops=3)
              Buffers: shared hit=4022316 read=234277
              ->  Parallel Index Scan using some_existing_idx on A a  (cost=0.69..785157.53 rows=356855 width=30) (actual time=0.101..731.991 rows=283612 loops=3)
                    Index Cond: (field1 = ANY ('{1,2,3,4,5,6,7,8}'::bigint[]))
                    Buffers: shared hit=632106 read=225426
              ->  Index Scan using b_pkey on B b  (cost=0.44..1.17 rows=1 width=22) (actual time=0.002..0.002 rows=1 loops=850836)
                    Index Cond: (id = a.ref_id)
                    Buffers: shared hit=3390210 read=8851
        ->  Hash  (cost=1.22..1.22 rows=22 width=34) (actual time=0.024..0.024 rows=22 loops=3)
              Buckets: 1024  Batches: 1  Memory Usage: 10kB
              Buffers: shared hit=3
              ->  Seq Scan on C c  (cost=0.00..1.22 rows=22 width=34) (actual time=0.012..0.014 rows=22 loops=3)
                    Buffers: shared hit=3
Planning Time: 5.382 ms
Execution Time: 1581.816 ms

Answer 1

a 和b 之间的嵌套循环左连接可能是这里最有效的方法。有 850000 次循环，但每次执行仅需 0.002 毫秒，总计约 1.9 秒。这是三个 worker 并行完成的，所以实际用时约为 0.6 秒。

这与 a 上的并行索引扫描的 0.7 秒一起构成了执行时间。

另一种方法是在 a 和 b 之间执行散列连接，这需要对 b 和 a 进行顺序扫描大哈希。要么顺序扫描的成本更高，要么 work_mem 配置得太小而无法包含生成的哈希值。

唯一的改进机会是提高 work_mem 并查看执行速度是否会稍微加快。

为了测试我的分析是否正确，尝试

SET enable_nestloop = off;

然后再次运行查询。如果这会使执行变慢，那么 PostgreSQL 做对了。