B-Tree索引中的最佳排序順序以支持對最近行的查詢？

假設我有一個表，其描述如下：

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

我不喜歡實際上不是 a而是 a的列的名稱“create_date”。改用**“created_at”**。date``timestamptz

由於created_atcan be NULL，這第 3 個變體將更快（即使不是很多）：

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

NULL預設情況下，值在最大值之後排序。DESCENDING排序順序是完美的反轉，所以NULL值排在第一位。看：

• 按 ASC 列排序，但首先是 NULL 值？

Postgres 可以以幾乎相同的速度向後掃描 B-tree 索引，因此您的兩個變體幾乎是相同的。但是運算符>=不包括NULL值（像大多數運算符一樣）。所以 Postgres 必須先分別跳過前導/尾隨NULL值。通常不貴，但仍然。

**DESC NULLS LAST**帶有(or )的索引NULLS FIRST首先具有最大值，然後具有最大值NULL（反之亦然），因此查詢可以直接從索引的頂部（底部）開始讀取。

如果不能有NULL值，就不會有明顯的差異。您應該聲明該列NOT NULL。（你應該這麼說的。）

如果插入帶有嚴格升序的時間戳（並且沒有更新！） - 或者如果對於自“昨天”以來最近插入的行至少是這樣，（相關）行會自動按時間戳進行物理分群。否則，它可以不時對行進行物理集群。（雖然不會干擾數據庫上的並發負載！）這可以產生更大的差異，因為它將必須讀取的數據頁數保持在最低限度。看：

• 為讀取性能配置 PostgreSQL

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

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

同樣，細節取決於完整的情況。有關的：

• PostgreSQL 中的覆蓋索引是否有助於 JOIN 列？

如果滿足某些先決條件，您現在可以獲得更便宜的僅索引掃描。在這種情況下，表中行的物理順序無關緊要。

---

哦，將該timestamptz列移動到表定義中的不同位置。由於對齊填充，您現在擁有它的方式最大化膨脹。列的任何其他位置timestamptz都更好。像：

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

看：

• 在 PostgreSQL 中計算和節省空間

引用自：https://dba.stackexchange.com/questions/294898