PostgreSQL 10：带LIMIT的时间范围分区查询触发全分区扫描问题

这是预期行为吗？ #

是的，这是PostgreSQL当前的预期行为。原因在于你的表是按created_at单天范围分区的，而查询过滤条件仅用到customer_id，未涉及分区键created_at——PostgreSQL无法提前判断哪些分区存在customer_id=123的数据，因此只能扫描所有分区，收集符合条件的记录后进行全局排序，最终取前10条。这种全局扫描+排序的操作在数据量较大时必然会很慢。

更优的解决方案 #

核心思路是避免扫描所有分区，只在必要的分区内查找数据，以下是几种可行的优化方式：

1. 按时间从新到旧逐个查询分区（最直接高效） #

既然表按天分区，你可以从最新的分区开始，逐个查询该用户的记录，每个分区最多取10条，最后合并结果再取前10。这种方式能充分利用已有的(customer_id, created_at DESC)索引，每个分区的查询都是快速的索引定位，无需扫描全分区。

示例SQL：

SELECT * FROM (
  -- 从最新分区开始，依次往前查询
  SELECT * FROM my_table_20240520 WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10
  UNION ALL
  SELECT * FROM my_table_20240519 WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10
  UNION ALL
  SELECT * FROM my_table_20240518 WHERE customer_id = 123 ORDER BY created_at DESC LIMIT 10
  -- 按需添加更早的分区，覆盖用户可能有数据的时间范围
) AS combined
ORDER BY created_at DESC LIMIT 10;

你担心的“多次尝试不同created_at值”其实是合理的——因为只需扫描用户可能存在数据的最近几个分区，而非所有分区，整体性能会比全局扫描提升很多。如果分区数量过多，也可以用递归CTE自动遍历分区，无需手动写分区名：

WITH RECURSIVE latest_data AS (
  -- 初始步骤：查询最新分区的用户数据
  SELECT *, created_at::date AS part_date
  FROM my_table
  WHERE created_at::date = (SELECT MAX(created_at::date) FROM my_table)
    AND customer_id = 123
  ORDER BY created_at DESC
  LIMIT 10
  UNION ALL
  -- 递归查询更早的分区，直到凑够10条记录
  SELECT t.*, p.part_date
  FROM (
    SELECT (SELECT MAX(created_at::date) FROM my_table WHERE created_at::date < ld.part_date) AS part_date
    FROM latest_data ld
    LIMIT 1
  ) p
  JOIN my_table t ON t.created_at::date = p.part_date AND t.customer_id = 123
  WHERE (SELECT COUNT(*) FROM latest_data) < 10
  ORDER BY t.created_at DESC
  LIMIT GREATEST(10 - (SELECT COUNT(*) FROM latest_data), 0)
)
SELECT customer_id, created_at
FROM latest_data
ORDER BY created_at DESC
LIMIT 10;

2. 添加时间范围过滤（最简单的业务优化） #

如果业务上能预估用户的活跃时间范围（比如用户最近30天有数据，或仅关心最近N天的记录），可以在查询中加入created_at的范围条件，触发PostgreSQL的分区裁剪，只扫描指定时间范围内的分区：

SELECT * 
FROM my_table 
WHERE customer_id = 123 
  AND created_at >= CURRENT_DATE - INTERVAL '30 days' -- 限制查询的时间范围
ORDER BY created_at DESC 
LIMIT 10;

这种方式无需修改表结构，只要时间范围设置合理，就能大幅减少扫描的分区数量，配合现有索引快速获取结果。

3. 调整分区策略（长期优化方案） #

如果上述方法无法满足需求，可以考虑调整分区策略：

• 改用复合分区：先按created_at范围（天）分区，再按customer_id哈希分区。这样查询customer_id=123时，能定位到每个天分区内对应的哈希子分区，减少扫描的分区数量。
• 改用customer_id范围分区：但如果customer_id分布分散，可能导致分区数量过多，需要根据实际数据量评估可行性。

内容的提问来源于stack exchange，提问作者Andrew