sql - Postgresql ORDER BY - 选择正确的索引

Question

T(user, timestamp,...) 表有 100 ml+ 记录 (PostgreSQL 9.1)。

表单的查询

SELECT * 
FROM T 
WHERE user='abcd' 
ORDER BY timestamp 
LIMIT 1

当有约 100000 条用户记录时，正在使用 timestamp 索引而不是用户索引。

使用时间戳索引总是会给出糟糕的结果(20+ 秒)，因为它最终会扫描所有记录。通过将查询更改为使用 ORDER BY DATE(timestamp) 来绕过 timestamp 索引将导致查询求助于用户索引并给出小于 100 毫秒的结果。

• 总内存:64 GB
• 共享缓冲区:16 GB
• 工作内存:32MB

为什么 postgresql 忽略 user 索引而使用 timestamp 索引(时间戳索引需要查看所有记录)？是否有任何可以更改的 postgresql 配置参数以使查询本身使用用户名索引？

Answer 1

好问题，我刚才已经解决了这个问题。

为什么会这样？

您应该查看 stats 中 user='abcd' 值的数量像这样:

SELECT attname, null_frac, ag_width, n_distinct,
       most_common_vals, most_common_freqs, histogram_bounds
  FROM pg_stats
 WHERE table_name='T';

我的猜测是 — 这个值经常出现，您会在 most_common_vals 输出中找到它。从 most_common_freqs 中选取相同的元素，您将得到该值的比率，将它乘以总行数(可以从 pg_class 获得)得到 的行数估计具有'abcd'值。

Planner 假定所有值都呈线性分布。实际上，情况当然有所不同。此外，目前没有 correlated stats (although some work is being done in this direction)。

因此，让我们采用 user='abcd' 值，在相应的 most_common_freqs 条目中具有 0.001 比率(每个问题)。这意味着值将每 1000 行出现一次(假设线性分布)。看起来，如果我们以任何方式 扫描表，我们将在大约 1000 行中找到我们的 user='abcd'。听起来应该很快! Planner 也“认为”相同，并在 timestamp 列上选择索引。

但事实并非如此。如果我们假设您的表 T 包含用户事件日志，并且 user='abcd' 在过去 3 周内休假，那么这意味着我们'在我们真正找到我们想要的行之前，我们必须从 timestamp 索引中读取很多行(3 周的数据)。好吧，作为 DBA，您知道这一点，但规划器假定线性分布。

那么，如何解决？

随着您对数据的了解越来越多，您将不得不欺骗计划者使用您需要的东西。

1. 使用 OFFSET 0 trick使用子查询:

   SELECT *
     FROM
     (
        SELECT * FROM T WHERE user='abcd' OFFSET 0
     )
     ORDER BY timestamp 
     LIMIT 1;
   

   这个技巧可以防止查询被内联，因此内部部分是自己执行的。

2. 使用 CTE (命名子查询):

   WITH s AS (
           SELECT * FROM T WHERE user='abcd'
   )
   SELECT *
     FROM s
    ORDER BY timestamp 
    LIMIT 1;
   

   根据文档:

   A useful property of WITH queries is that they are evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries.

3. 使用 count(*) 进行聚合查询:

   SELECT min(session_id), count(*) -- instead of simply `min(session_id)`
     FROM T 
    WHERE user='abcd' 
    ORDER BY timestamp 
    LIMIT 1;
   

   这不是真的适用，但我想提一下。

请考虑升级到 9.3。

附言有关行估计的更多信息 in the docs of course .