我有一個 GIST 索引和一個正常索引，一個用於過濾，一個用於排序，但 Postgres 不會同時使用兩者

我有一個包含三列的表：兩列可用於過濾表，最後一列可用於對其進行排序：

create table thing(
 id serial primary key,
 location geometry(Point, 4326) not null,
 created_at timestamptz not null default (now() at time zone 'utc'),
 priority float not null
);

我一直在使用索引來支持以下查詢：

select id
from thing
where st_dwithin(location, 'SRID=4326;Point($0 $1)'::geometry, $2)
 and created_at >= now() - interval '$3 days'
order by priority
offset $4
limit $5;

理想情況下，此查詢可以結合位置、created_at 和優先級的索引以獲得最佳性能。通常我會想像索引using btree (priority, location, created_at)會很好地支持這樣的查詢，在過濾器期間利用索引並提供預先排序的結果。但是，過濾器的 st_dwithin 部分不受過濾器的幫助，因為它不是gist索引。

如果我創建索引using gist (priority, location, created_at)，則查詢計劃在過濾期間使用索引，但不提供預先排序的結果，並且在沒有索引輔助的情況下對結果進行排序。

在這一點上，我認為我們必須拆分索引，但是using gist(location, created_at)如果using btree(priority)查詢$5 <= 20計劃使用 btree 索引並且$5 > 20查詢計劃使用 gist 索引。

有誰知道為什麼會這樣，以及我可以做些什麼來優化過濾和排序的性能？

我會扭轉這個問題。它可以按照您想要的方式組合索引的機制是什麼？點陣圖不能提供命令，除了它的固有性質。還有什麼其他方法可以用來組合兩個索引？它會比排序更快嗎？

在所有 3 列上都有一個 GiST 索引，您可以通過像這樣編寫 ORDER BY 來讓它使用 KNN 機制：

ORDER BY :min_priority <-> priority

其中 :min_priority 是一個常數，等於或低於可能使用的最低優先級。在我的手中，這將起作用，在使用索引進行過濾和排序的意義上，也許一旦你強迫它的手（例如通過設置 enable_sort=off）。但它可能仍然比其他一些理論上不太令人滿意的方法慢。此外，我不相信 KNN 程式碼完全沒有錯誤。

性能在很大程度上取決於每個子句的選擇性以及用於 OFFSET 和 LIMIT 的實際值是什麼。在某些情況下，一種方法會更快，而在其他情況下，另一種可能會更快。此外，GiST 估計根本不是很穩健，因此當實際更快的方法發生變化時，您不能指望它來改變計劃。

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