postgresql - 加入 3 个表的索引 ID 出乎意料地慢

Question

我有三个构建层次结构的表:

• 具有主键id 的表customer
• 具有主键 id 和外键 fk_customer(索引)的表 portfolio
• 具有主键 id 和外键 fk_portfolio(索引)的表 position

客户有投资组合(但有些没有)，投资组合有头寸(但有些没有)。

我有一个基本上选择此层次结构的 View ，通常使用有关客户 ID、投资组合 ID 或位置 ID 的子句查询。 使用此 View 进行选择时的性能出奇地差，并且在我希望执行时间低于 10 毫秒的情况下占用了超过一秒的时间。

为了分析性能，我将查询隔离并简化如下:

SELECT bp.id, ptf.id, pos.id FROM customer bp
left outer join portfolio ptf on ptf.fk_customer = bp.id
left outer join position pos on pos.fk_portfolio = ptf.id
WHERE ptf.id IN (1, 2)
OR pos.id IN (3, 4)

在具体设置中(7 万个客户、10 万个投资组合、60 万个职位)，此查询几乎需要一秒钟(返回大约 10 行)。我在 Oracle 和 Postgres 上重建了这个设置(相同的数据，相同的记录数)，两者都显示出相同的性能问题。
当我稍微改变 View 时(WHERE pos.fk_portfolio IN (1, 2))，执行时间约为 0.1 毫秒，但没有返回没有头寸的投资组合。

Postgres 上的执行计划:

Gather  (cost=22125.87..27689.07 rows=13 width=24) (actual time=703.717..782.415 rows=9 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Hash Left Join  (cost=21125.87..26687.77 rows=5 width=24) (actual time=700.739..751.123 rows=3 loops=3)
        Hash Cond: (ptf.id = pos.fk_portfolio)
        Filter: ((ptf.id = ANY ('{1,2}'::bigint[])) OR (pos.id = ANY ('{3,4}'::bigint[])))
        Rows Removed by Filter: 202202
        ->  Parallel Hash Left Join  (cost=3057.84..5195.48 rows=42990 width=16) (actual time=70.319..171.940 rows=39930 loops=3)
              Hash Cond: (bp.id = ptf.fk_customer)
              ->  Parallel Index Only Scan using sys_c0011416 on customer bp  (cost=0.29..1440.43 rows=29642 width=8) (actual time=0.026..20.169 rows=23714 loops=3)
                    Heap Fetches: 0
              ->  Parallel Hash  (cost=2298.91..2298.91 rows=60691 width=16) (actual time=69.626..69.627 rows=34392 loops=3)
                    Buckets: 131072  Batches: 1  Memory Usage: 5920kB
                    ->  Parallel Seq Scan on portfolio ptf  (cost=0.00..2298.91 rows=60691 width=16) (actual time=0.027..38.559 rows=34392 loops=3)
        ->  Parallel Hash  (cost=13796.90..13796.90 rows=245690 width=16) (actual time=415.120..415.121 rows=196552 loops=3)
              Buckets: 131072  Batches: 16  Memory Usage: 2816kB
              ->  Parallel Seq Scan on "position" pos  (cost=0.00..13796.90 rows=245690 width=16) (actual time=0.009..222.681 rows=196552 loops=3)
Planning Time: 1.280 ms
Execution Time: 782.808 ms

禁用序列扫描(set enable_seqscan = false)也无济于事。我也是

• 确保索引(在外键约束上)存在并且处于事件状态
• 更新统计信息并压缩表格(对所有 3 个表格进行 VACUUM ANALYZE)
• 重新索引表(对所有 3 个表进行 REINDEX)

我发现了其他表达查询的方式(使用两个单独的选择和 id 过滤，然后将两者合并)，它们显示出出色的性能，但是没有一种方式允许我创建 View 之后我可以在其中按客户/投资组合/职位 ID 进行过滤。
联合示例(执行时间 <1 毫秒):

(select bp.id, ptf.id, pos.id from customer bp
    left outer join portfolio ptf on ptf.fk_customer = bp.id
    left outer join position pos on pos.fk_portfolio = ptf.id
    where ptf.id IN (1, 2))
UNION
(select bp.id, ptf.id, pos.id from customer bp
    left outer join portfolio ptf on ptf.fk_customer = bp.id
    left outer join position pos on pos.fk_portfolio = ptf.id
    where pos.id IN (3, 4))

我已经无计可施了——我本以为查询会非常快，因为:

• 它实际上并不获取任何数据(仅获取主键)
• 仅按主键过滤，并且
• 通过索引外键列连接。

我希望你们中的任何人都能就性能如此糟糕(在 Postgres 和 Oracle 上)的原因提出一些见解，并就如何解决这个问题提出建议。

编辑:
我正在通过不支持联合的 JPA(Java Persistence API)查询数据。但是，我可以将联合用作 View 定义的一部分，因为我只需要通过 JPA 传递条件(在子句中)。

Answer 1

您肯定需要带有UNION 的变体，因为OR 不能很好地执行。

问题是 PostgreSQL 无法从 IN 列表中推断出它可以将外部连接转换为内部连接。

尝试编写内部连接:

(select bp.id, ptf.id, pos.id from customer bp
    join portfolio ptf on ptf.fk_customer = bp.id
    left outer join position pos on pos.fk_portfolio = ptf.id
    where ptf.id IN (1, 2))
UNION
(select bp.id, ptf.id, pos.id from customer bp
    join portfolio ptf on ptf.fk_customer = bp.id
    join position pos on pos.fk_portfolio = ptf.id
    where pos.id IN (3, 4));

或者尝试添加一个可能给 PostgreSQL 线索的条件:

(select bp.id, ptf.id, pos.id from customer bp
    left outer join portfolio ptf on ptf.fk_customer = bp.id
    left outer join position pos on pos.fk_portfolio = ptf.id
    where ptf.id IN (1, 2))
      and ptf.id IS NOT NULL
UNION
(select bp.id, ptf.id, pos.id from customer bp
    left outer join portfolio ptf on ptf.fk_customer = bp.id
    left outer join position pos on pos.fk_portfolio = ptf.id
    where pos.id IN (3, 4)
      and pos.id IS NOT NULL);

我不确定第二个查询是否能解决问题。