sql - 如何优化只能根据两个表区分数据的 2 表查询？

Question

我有以下2个表和数据分布:

drop table if exists line;
drop table if exists header;

create table header (header_id serial primary key, type character);
create table line (line_id serial primary key, header_id serial not null, type character, constraint line_header foreign key (header_id) references header (header_id)) ;
create index inv_type_idx on header (type);
create index line_type_idx on line (type);

insert into header (type) select case when floor(random()*2+1) = 1 then  'A' else 'B' end from generate_series(1,100000);
insert into line (header_id, type) select header_id,  case when floor(random()*10000+1) = 1 then (case when type ='A' then 'B' else 'A' end) else type end from header, generate_series(1,5);

• header表有 10 万行:type 的 50% A 和 B 的 50%
• line表有 500K 行:
  • 每个header有 5 line小号
  • 总体上有 50% 的行 type A 和 B 的 50%
  • type的 line与它的header相同在 99.99% 的情况下，只有 0.01% 的情况不同

数据分布:

# select h.type header_type, l.type line_type, count(*) from line l inner join header h on l.header_id = h.header_id group by 1,2 order by 1,2;
 header_type | line_type | count  
-------------+-----------+--------
 A           | A         | 250865
 A           | B         |     25
 B           | A         |     29
 B           | B         | 249081
(4 rows)

我需要获取所有 line与 type B谁的header是 A。即使总量非常有限(500000 行中的 25 行)，我获得的计划 (PostgreSQL 10) 如下，它在两个表中执行顺序扫描:

explain
select * from line l
   inner join header h on l.header_id = h.header_id
where h.type ='A' and l.type='B';

                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Hash Join  (cost=2323.29..14632.89 rows=125545 width=19)
   Hash Cond: (l.header_id = h.header_id)
   ->  Seq Scan on line l  (cost=0.00..11656.00 rows=248983 width=13)
         Filter: (type = 'B'::bpchar)
   ->  Hash  (cost=1693.00..1693.00 rows=50423 width=6)
         ->  Seq Scan on header h  (cost=0.00..1693.00 rows=50423 width=6)
               Filter: (type = 'A'::bpchar)
(7 rows)

有什么方法可以优化这种数据歧视非常高但仅当组合来自多个表的信息时的查询？

当然，作为解决方法，我可以对存储在 line 中的信息进行非规范化处理来自 header 的信息这将使该查询的性能更高。但如果可能的话，我宁愿不必这样做，因为我需要维护这些重复的信息。

alter table line add column compound_type char(2);
create index compound_idx on line (compound_type);

update line l
   set compound_type = h.type || l.type
  from header h
 where h.header_id = l.header_id;

# explain select * from line where compound_type = 'BA';
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Scan using compound_idx on line  (cost=0.42..155.58 rows=50 width=13)
   Index Cond: (compound_type = 'BA'::bpchar)
(2 rows)

Answer 1

1) 您可以使用具有适当索引的物化 View 。它可以在“后台”更新。否则它类似于您的组合索引。

2) 如果在 (line.header_id, line.type) 上创建索引并像这样强制子查询，则可以将搜索反向到标题到行:

select header_id 
from header h 
where type='A' and 
    exists(select * from line l where l.header_id=h.header_id and l.type='B')

在获得所有标题后，再次选择具有相应 header_id 的行。

将类型包含到某些 header 索引中可能是个好主意，这样 2 个索引就是查找所需的一切。

它仍然会在 header 索引中读取约 50K 行，并在第二个索引中查找每一行。一般来说它不是很有效，但如果索引完全适合内存，它可能不会那么糟糕。