PostgreSQL按日期分区查询优化

Question

我们有一张包含每月约10亿条记录的表格。考虑到18个月的历史数据，我们就需要处理大约180亿条记录。

该表格按照日期每周分区（因此我们大约有74个分区）。

对于我们的某个查询，我们需要获取一个指定单元最近的1000条记录。类似这样：

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

问题在于，我们在解释中得到了以下结果:

Limit (cost=96181.06..96181.09 rows=10 width=12)

-> Sort (cost=96181.06..102157.96 rows=2390760 width=12)

Sort Key: unit_position .obs_time

 ->  Result  (cost=0.00..44517.60 rows=2390760 width=12)
     ->  Append  (cost=0.00..44517.60 rows=2390760 width=12)

     ->  Seq Scan on unit_position (cost=0.00..42336.00 rows=2273600 width=12)

     ->  Seq Scan on unit_position_week350 unit_position (cost=0.00..21.60 rows=1160 width=12)

     ->  ... (ALL OTHER PARTITIONS) ...

     ->  Seq Scan on unit_position_week450 unit_position   (cost=0.00..21.60 rows=1160 width=12)

另一方面，如果我们得到这样的查询（将查询限制在我们可以获取1000条记录的第一个间隔内），我们可以获得比原来快两倍的结果:

  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状态来限制QUERY到PARTITIONS的约束条件。是否有一种方法强制执行此操作？（例如，在ORACLE中，可以向DB引擎提供有关如何执行某些查询的建议，尽管我也不知道是否对分区执行此操作）

手动执行每个分区（给出间隔）的开销会导致更差的结果（这样做实际上可能是在没有分区的情况下工作，最好有不同的表）。

还有其他建议吗？

- RGPT

Answer 1

这个函数将动态查询一周的数据，最多查询到限制，希望能利用分区。 SQL Fiddle

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);

- Clodoaldo Neto

Answer 2

PostgreSQL 的分区是一个巧妙的技巧，其中一个领域就是这样。没有“智能分区扫描”节点类型，可以锁定所有分区，但只扫描它们直到满足行计数要求。

仅使用 constraint_exclusion 进行分区扫描限制，这要求查询规划器能够从查询中的常量证明不需要分区。

您的问题的适当解决方案将需要在 PostgreSQL 中添加一种新的扫描类型，其中 Pg 在查询开始时锁定所有分区，但仅扫描它们直到满足外部计划节点的行数要求。

您已经找到了其中一个更好的可用解决方法，即添加一个常量来限制扫描的分区。虽然没有查询提示来限制扫描的分区，但您可以自己编写一个明确的计划，例如：

  SELECT code, obs_time
    FROM (
        SELECT * FROM unit_position_week_350
        UNION ALL
        SELECT * FROM unit_position_week_349
        UNION ALL
        SELECT * FROM unit_position_week_348
        UNION ALL
        SELECT * FROM unit_position_week_347
        UNION ALL
        SELECT * FROM unit_position_week_346
        UNION ALL
        SELECT * FROM unit_position_week_345
   ) unit_position_350_to_345
   WHERE unit_id = 1
ORDER BY obs_time DESC LIMIT 1000;

...但我还没有测试它的计划和性能是否良好。如果规划器未将该条件自行向下推送，则您可能需要将ORDER BY移动到子查询甚至是unit_id中。

- Craig Ringer

---

在我的文本（问题）的结尾，我已经谈到了这个情况。这是我已经尝试过的事情之一，实际上我得到了更糟糕的结果（不是很糟糕，但却更糟糕）。在你的答案中，你无论如何都必须执行完整的查询，因为你在主查询中有ORDER BY。正如你所说，你需要通过子查询来完成，否则你会得到更糟糕的结果。 - RGPT

@RGPT 很不幸，你在这方面没有更好的选择；正如我所说，Pg中的分区有些原始，并且它有一些缺点和优点。 - Craig Ringer