postgre如何优化SQL查询(根据历史/实际版本控制模式中的条件进行搜索)

Question

我在PostgreSQL数据库中有实际表和历史表。

create table actual (id int, name text, version int);
create table history (id int, name text, version int, actual_id int);

当记录发生更改时，它会被复制到历史记录表中，并且实际版本会增加。无法删除行。

例如，如果我们有3条记录A1、B1、C1(1是版本号)并更改了B的名称，那么实际的表将包含A1、B2、C1和历史-B1。然后我们可以更改C的名称，实际数据将是A1、B2、C3和历史-B1、C1

不幸的是，这需要使用UNION ALL才能根据特定版本的条件搜索记录：

select * from (
select row_number() over (partition by id order by version desc) rn, 
id, name, version 
from 
(
select h.actual_id as id, h.name, h.version from history h
union all
select * from actual 
) x
where version <= 2 
) y
where rn = 1 and name like '%something%';

这显然是name在y记录集中的完全扫描(尽管actual和history表在name列上都有索引)。我无法将and name like '%something%'移到where version <= 2中，因为它可以在以前的版本中找到名称，但在最新版本中找不到。

如何优化此查询？是否可以告诉postgres在实际表和历史表中使用索引

下面是整个测试用例：

create table actual (id int, name text, version int);
create table history (id int, name text, version int, actual_id int);
insert into actual values (1, 'A', 3);
insert into actual values (2, 'B', 2);
insert into actual values (3, 'C', 2);
insert into actual values (4, 'D_changed', 5);
insert into history values (1, 'A', 1, 1);
insert into history values (2, 'B', 1, 2);
insert into history values (3, 'C', 1, 3);
insert into history values (4, 'D_old', 4, 4);
insert into history values (5, 'D_very_old', 2, 4);
select * from (
select row_number() over (partition by id order by version desc) rn, 
id, name, version 
from 
(
select h.actual_id as id, h.name, h.version from history h
union all
select * from actual 
) x
where version <= 5 -- and name like '%old%' - this finds wrong record ver=4
) y
where rn = 1 and name like '%old%';

Answer 1

我认为使用版本号的整个方法不是一个好主意。我设法将其替换为一种更传统的方式，将from_date和to_date列添加到表中，查询变得如此简单：

select * from
(
select h.actual_id as id, h.name, h.start_date, h.end_date from history h
union all
select * from actual 
) x
where ? between start_date and end_date