问题:PostgreSQL分区查询按日期优化

我们有一个表,每月大约有 10 亿条记录。考虑到 18 个月的历史,我们谈论的是 180 亿条记录。

该表每周按日期分区(因此我们有大约 74 个分区)。

对于我们的一个查询,我们需要获取一个给定单元的最后 1000 条记录。像这样的东西

  SELECT code, obs_time
    FROM unit_position 
   WHERE unit_id = 1
ORDER BY obs_time DESC LIMIT 1000;

问题是,为此我们在解释中有以下结果:

限制(成本u003d96181.06..96181.09 行u003d10 宽度u003d12)

-> 排序(成本u003d96181.06..102157.96 行u003d2390760 宽度u003d12)

排序键:unit_position .obs_time

-> 结果(成本u003d0.00..44517.60 行u003d2390760 宽度u003d12)

-> 追加(成本u003d0.00..44517.60 行u003d2390760 宽度u003d12)

-> unit_position 上的 Seq 扫描(成本u003d0.00..42336.00 行u003d2273600 宽度u003d12)

-> unit_position_week350 unit_position 上的 Seq 扫描(成本u003d0.00..21.60 行u003d1160 宽度u003d12)

-> ...(所有其他分区)...

-> unit_position_week450 unit_position 上的 Seq 扫描(成本u003d0.00..21.60 行u003d1160 宽度u003d12)

另一方面,如果我们得到这样的查询(将查询限制在我们可以得到 1000 条记录的第一个区间),我们可以得到 2 倍以上的结果:

  SELECT fake, obs_time
    FROM unit_position 
   WHERE unit_id = 1
     AND obs_time >= NOW() - '7 weeks'::interval
ORDER BY obs_time DESC LIMIT 1000;

问题是,考虑到我们是按 obs_time 排序的,有没有办法让查询使用分区并且只搜索需要的前 n 个分区?

在大多数情况下,结果将在最近的 4 个分区中(因此它只会搜索这 4 个分区),并且只有在极少数情况下,它才需要搜索所有分区。

如果在获得 n 个分区(按顺序)后找到 1000 个结果,则不会考虑其余分区(丢弃数十亿条记录)。测试/解释表明 PostgreSQL 没有这样做。它实际上适用于所有分区(如果它没有获得将查询限制为分区约束的 WHERE 状态。有没有办法强制执行此操作?(例如,在 ORACLE 中,可以向数据库引擎提出如何执行一些查询,即使我也不知道是否对分区执行此操作)

手动执行每个分区(给出间隔)的开销给我们带来了最差的结果(并且这样做我们实际上可以在没有分区的情况下工作,最好有不同的表)。

还有其他建议吗?

解答

此功能将一次动态查询一周,希望能利用分区的优势。SQL 小提琴

create or replace function unit_position_limited_by(l integer)
returns setof unit_position
language plpgsql as $function$

declare
    week timestamp := date_trunc('week', transaction_timestamp());
    total integer := 0;
    inserted integer;
    not_exists boolean;
begin
    loop
        return query execute $$
            select *
            from unit_position
            where
                unit_id = 1
                and obs_time >= $1 and obs_time < $2
            order by obs_time desc
            limit $3
        $$ using week, week + interval '1 week', l - total;
        get diagnostics inserted := row_count;
        total := total + inserted;
        exit when total = l; 
        if inserted = 0 then
            execute $$
                select not exists (
                    select 1
                    from unit_position
                    where obs_time < $1
                    )
            $$ into not_exists using week;
            exit when not_exists;
        end if;
        week := week - interval '1 week';
    end loop;
end; $function$;

要从中选择:

select *
from unit_position_limited_by(1000);