jsonb 數據上的 Postgresql 多欄位索引在未找到欄位時執行全表掃描

我有一張包含數百萬條記錄的表。對於給定的欄位，我需要按時間戳查詢最後添加的記錄。非常簡單的東西，對 SQL 來說微不足道：

CREATE TABLE records
(
   id integer,
   "timestamp" integer,
   type text
);
CREATE INDEX idx_type_time_sql
ON records (type ASC, timestamp DESC);

查詢速度很快，甚至可以搜尋表中不存在的類型。

select * from records where type = 'KNOWN' order by timestamp desc limit 1
select * from records where type = 'UNKNOWN' order by timestamp desc limit 1

我也幾乎可以讓它使用 NOSQL（又名包含所有對象屬性的 jsonb 欄位）：

CREATE TABLE records
(
   id integer,
   json jsonb NOT NULL
)
CREATE INDEX idx_timestamp
ON records (((json->'timestamp')::bigint));

找到類型時，查找記錄的速度很快（幾毫秒）。但是，如果在表中找不到類型，則無法使用索引。它會進行一次全表掃描，大約需要 12 秒。

-- fast:
select * from records where json->>'type' = 'KNOWN'
order by (json->'timestamp')::bigint desc limit 1;
-- slow:
select * from records where json->>'type' = 'UNKNOWN'
order by (json->'timestamp')::bigint desc limit 1;

我嘗試了許多不同類型的 jsonb 索引和查詢，但都沒有運氣，例如：

CREATE INDEX IF NOT EXISTS idx_type_timestamp ON records ( (json -> 'type'), ((json -> 'timestamp')::bigint));

在查詢未知值時，有什麼方法可以讓 postgresql jsonb 索引和一個好的老式 SQL 索引一樣工作？或者這只是jsonb的一個缺點？

密切注意將索引語法與查詢語法匹配是解決這個問題所需的全部（感謝@jjanes）。一旦你這樣做了，成功的方法與索引標準 SQL 列非常相似。

指數：

CREATE INDEX IF NOT EXISTS idx_json_pair ON records ((json->>'type'),((json->'timestamp')::bigint));

現在，命中和未命中查詢都已完全索引且速度很快：

select * from records where json->>'type' = 'KNOWN' order by (json->'timestamp')::bigint desc limit 1
select * from records where json->>'type' = 'UNKNOWN' order by (json->'timestamp')::bigint desc limit 1

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