主页 / dba / 问题 / 294898
Accepted
Sahap Asci
Asked: 2021-06-28 08:49:15 +0800 CST

B树索引中的最佳排序顺序以支持对最近行的查询？

  • 772

假设我有一个表，其描述如下：

create table my_table (
  id serial, 
  create_date timestamp with time zone default now(),
  data text
);

和类似的查询：

select * from my_table
where create_date >= timestamp with time zone 'yesterday'

理论上哪个索引会更快，为什么？

create index index_a on my_table (create_date);

create index index_b on my_table (create_date DESC);
postgresql index

1 个回答

  1. Best Answer

    我不喜欢实际上不是 a而是 a的列的名称“create_date”。改用“created_at”。datetimestamptz

    由于created_atcan be NULL，这第 3 个变体将更快（即使不是很多）：

    CREATE INDEX index_c ON my_table (created_at DESC NULLS LAST);

    NULL默认情况下，值在最大值之后排序。DESCENDING排序顺序是完美的反转，所以NULL值排在第一位。看：

    Postgres 可以以几乎相同的速度向后扫描 B-tree 索引，因此您的两个变体几乎是相同的。但是运算符>=不包括NULL值（像大多数运算符一样）。所以 Postgres 必须先分别跳过前导/尾随NULL值。通常不贵，但仍然。

    DESC NULLS LAST带有(or )的索引NULLS FIRST首先具有最大值，然后具有最大值NULL（反之亦然），因此查询可以直接从索引的顶部（底部）开始读取。

    如果不能有NULL值，就不会有明显的差异。您应该声明该列NOT NULL。（你应该这么说的。）

    如果插入带有严格升序的时间戳（并且没有更新！） - 或者如果对于自“昨天”以来最近插入的行至少是这样，（相关）行会自动按时间戳进行物理聚类。否则，它可以不时对行进行物理集群。（虽然不会干扰数据库上的并发负载！）这可以产生更大的差异，因为它将必须读取的数据页数保持在最低限度。看：

    如果您的表很大，则部分索引可以支付：

    CREATE INDEX index_c_partial ON my_table (created_at DESC NULLS LAST)
WHERE  created_at >= '2021-06-26 0:0';  -- recent but before yesterday

    它切断了大部分旧行，以便索引缩小到一小部分。

    但是由于您的截止 ( 'yesterday') 是一个移动目标，您必须不时重新创建该索引以删除旧元组，否则收益会随着时间的推移而恶化。就像，每天，每周，每月 - 你决定。

    使用暖缓存，该部分索引不会比完整索引快多少，但由于它非常小，因此它留在缓存中的机会相应更大（取决于您的完整设置），这通常会产生很大的不同。（而且它一开始并没有占用那么多资源。）

    由于我们现在有这么小的索引，虽然我们只处理这么少的列（或者您实际上不需要SELECT *开始？！），我们不妨将其设为覆盖索引（Postgres 11 或更高版本）：

    CREATE INDEX index_c_partial_covering ON my_table
   (created_at DESC NULLS LAST) INCLUDE (id, data)
WHERE  created_at >= '2021-06-26 0:0';

    同样，细节取决于完整的情况。有关的：

    如果满足某些先决条件，您现在可以获得更便宜的仅索引扫描。在这种情况下，表中行的物理顺序无关紧要。

    哦，将该timestamptz列移动到表定义中的不同位置。由于对齐填充，您现在拥有它的方式最大化膨胀。列的任何其他位置timestamptz都更好。喜欢：

    CREATE TABLE my_table (
, created_at timestamptz DEFAULT now() NOT NULL
, id serial NOT NULL PRIMARY KEY
, data text
);

    看：

    • 4

相关问题