存在条件下的索引

Question

假设您有一个简单的一对多(主细节(模式，其索引为：

create table master (
id serial primary key,
foo integer not null
);
create index master_foo on master (foo);
create table detail (
id serial primary key,
master_id integer not null references master (id),
bar integer not null
);
create index detail_master_id on detail (master_id);
create index detail_bar on detail (bar);
create index detail_master_id_bar on detail (master_id, bar);

假设你需要在主表上运行一个查询，在OR中有一些关于特定细节存在的条件(33和44当然可以是任意值，事先不知道(：

select *
from master
where foo = 33
or exists (
select 1
from detail
where detail.master_id = master.id
and bar = 44
);

此查询在细节上使用任何可用索引，但在master上没有

Seq Scan on master  (cost=0.00..18508.10 rows=1136 width=8)
^^^^^^^^^^^^^^^^^^
Filter: ((foo = 33) OR (alternatives: SubPlan 1 or hashed SubPlan 2))
SubPlan 1
->  Index Only Scan using detail_master_id_bar on detail  (cost=0.15..8.17 rows=1 width=0)
Index Cond: ((master_id = master.id) AND (bar = 44))
SubPlan 2
->  Bitmap Heap Scan on detail detail_1  (cost=4.23..14.79 rows=10 width=4)
Recheck Cond: (bar = 44)
->  Bitmap Index Scan on detail_bar  (cost=0.00..4.23 rows=10 width=0)
Index Cond: (bar = 44)

在现实世界中，主人是一张巨大的桌子。我如何重新排列这个查询(或数据结构本身，带触发器？(，以便不必对master执行完全扫描，这是我需要避免的？

Answer 1

将其拆分为两个查询，或者至少尝试使用子查询避开OR。我会选择：

explain analyse select master.* from master
join detail on detail.master_id = master.id and detail.bar = 44
union all
select master.* from master where foo = 33;
QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
Append  (cost=0.57..24.95 rows=2 width=8) (actual time=0.027..0.031 rows=1 loops=1)
->  Nested Loop  (cost=0.57..16.61 rows=1 width=8) (actual time=0.014..0.015 rows=0 loops=1)
->  Index Scan using detail_bar on detail  (cost=0.28..8.30 rows=1 width=4) (actual time=0.013..0.014 rows=0 loops=1)
Index Cond: (bar = 44)
->  Index Scan using master_pkey on master  (cost=0.29..8.31 rows=1 width=8) (never executed)
Index Cond: (id = detail.master_id)
->  Index Scan using master_foo on master master_1  (cost=0.29..8.31 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (foo = 33)
Planning Time: 1.134 ms
Execution Time: 0.085 ms

演示：db<>fiddle