具有最新時間戳的行

如何獲取列中具有最新值的TIMESTAMPZ行？需要索引嗎？索引會改變策略嗎？行為會因數據庫而異（我使用的是 Postgres 9.4）嗎？

我的應用程序從數據饋送中記錄數據。另一個過程無休止地查詢以獲取最新的最新條目。較舊的數據有時可能來自二手來源。因此，最近插入的行通常但不一定是最新數據。

我正在使用這種 SQL，其中when_是一TIMESTAMP WITH TIME ZONE列：

SELECT *
FROM my_table_ 
ORDER BY when_ DESC
LIMIT 1
;

此程式碼有效（如果數據中沒有 NULL 值！）。但是可能有幾百萬行，每 10 秒查詢一次，我擔心性能。

列上沒有任何索引when_，此語句是否需要對所有行進行全面掃描？

添加索引會改變性能嗎？Postgres 會自動掃描索引以定位最近的行，還是我必須做一些事情來進行索引掃描？

使用when_列上的索引，我應該更改此 SQL 以使用其他查詢方法/策略嗎？

還有其他方法可以收集新插入的行嗎？我使用UUID而不是SERIAL 類型作為我的主鍵，並且可能在多個數據庫實例之間聯合數據，因此排除了檢查不斷增加的整數。

基本答案

由於您選擇了幾個大列，因此僅索引掃描可能不是一個可行的選擇。

此程式碼有效（如果數據中沒有 NULL 值！）

雖然未定義列NOT NULL，但添加NULLS LAST到排序順序以使其在任何情況下都可以正常工作，即使有NULL值也是如此。理想情況下，也可以在相應索引中使用該子句：

SELECT <some big columns>
FROM   my_table_ 
ORDER  BY when_ DESC **NULLS LAST**
LIMIT  1;

• PostgreSQL 按日期時間 asc 排序，先為空？

列上沒有任何索引when_，此語句是否需要對所有行進行全面掃描？

是的。沒有索引，就沒有其他選擇了。（好吧，還有一個表分區，其中鍵列上的索引不是嚴格要求的，它可以幫助分區修剪。但您通常也會在鍵列上有一個索引。）

使用when_列上的索引，我應該更改此 SQL 以使用其他查詢方法/策略嗎？

**基本上，這是一個完美的查詢。**有一些選項與高級索引相結合：

---

先進技術

假設一個NOT NULL列。否則，NULLS LAST按照上面的建議添加到索引和查詢中。

你有一個不斷湧入的行，後來when_. 假設最新的_when 不斷增加並且從不（或很少）減少（最新的行被刪除/更新），您可以使用非常小的部分索引。

基本實現：

1. 執行一次查詢以檢索最新的when_，減去一個安全邊距（以防止失去最新的行）並IMMUTABLE基於它創建一個函式。基本上是一個“假全域常量”：

CREATE OR REPLACE FUNCTION **f_when_cutoff()**
 RETURNS timestamptz LANGUAGE sql COST 1 IMMUTABLE PARALLEL SAFE AS
$$SELECT timestamptz '2015-07-25 01:00+02'$$;

PARALLEL SAFE僅在 Postgres 9.6 或更高版本中。 2. 創建不包括舊行的部分索引：

CREATE INDEX my_table_when_idx ON my_table_ (when_ DESC)
**WHERE when_ > f_when_cutoff();**

對於數百萬行，大小的差異可能是巨大的。這只有在索引小得多的情況下才有意義。只有一半大小或其他東西不會削減它。索引訪問本身並不會因更大的索引而減慢很多。它主要是索引的絕對大小，需要讀取和記憶體。（並且可能避免額外的索引寫入，但在你的情況下幾乎沒有。） 3. 在所有相關查詢中使用該函式。包括相同的WHERE條件（即使在邏輯上是多餘的）以使查詢計劃者相信索引是適用的。對於簡單查詢：

SELECT <some big columns>
FROM   my_table_ 
**WHERE when_ > f_when_cutoff()**
ORDER  BY when_ DESC
LIMIT  1;

索引的大小隨著新的（以後的）條目而增長。使用較晚的時間戳重新創建函式，並且REINDEX不時地重新創建沒有或很少並發訪問的函式。只有在添加了相關數量的行後才重新索引。幾千個條目並不重要。我們這樣做是為了切斷數百萬人的生命。

它的美妙之處：查詢不會改變。

具有自動更新部分索引的功能的實現：

• 從大表中獲取每個父母的最新孩子 - 查詢太慢

更一般的建議：

• 日期索引優化

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