PostGIS - 索引 - 加速查詢

我有這個查詢（從 PHP 呼叫，因此是$變數）：

SELECT a.name,
     St_AsText(a.geom), 
     b.name AS name_b,
     b.id
FROM a
LEFT JOIN b ON b.id = a.b_id
WHERE UNACCENT(LOWER(a.name)) LIKE UNACCENT('$searched%')
ORDER BY a.geom <-> 'SRID=4326;POINT($lat $lon)'::geometry         
LIMIT 2;

有lower(a.name) BTree索引a.name，有GIST索引，a.geom並且b.id是主鍵

它執行大約 40 毫秒$searched = "Maria%"。現在，如果我設置$searched = "Mariasomething some%"，時間會上升到 2 秒。

為什麼？它可以加速或以更穩定的方式執行嗎？

解釋

我們必須查看表定義、基數和EXPLAIN輸出才能確定，但原因很可能是這樣的：

只能使用您的空間 GiST 索引a.geom。btree 索引不適用。Postgres 遍歷“最接近”的行，直到找到與您的謂詞匹配的前兩個。

通常，更嚴格的條件會使查詢更快，因為需要獲取（和排序）的行更少。但不是在這個星座中：較少的合格行無助於最近鄰搜尋的小LIMIT- au contrair。有兩種選擇：

1. 基於 GiST 索引的“最近鄰”搜尋——只要一些最接近的行符合條件，它就很快——如果你WHERE完全刪除該子句，則最快，試試吧！如果必須過濾許多行，它會變得越來越昂貴。基本上：您的模式越長，查詢的成本就越高。
2. 順序掃描，它必須解決選擇性條件的相同問題：過濾許多行，直到找到一些候選。該方法更快，但必須讀取整個表，如果找到多於幾行，則最終按距離排序會變得昂貴。

基本上，Postgres 被這個邪惡的查詢困在了一個艱難的境地之間。

使固定

您將需要一個unaccent(lower(name))不可能的索引，因為unaccent()is only STABLE， not IMMUTABLE。IMMUTABLE但是您可以使用函式包裝器解決此限制- 正確完成：

• PostgreSQL 是否支持“不區分重音”排序規則？

最好的索引似乎仍然很棘手，取決於您的完整情況。

由於您匹配左錨定模式，因此我將使用上面連結答案中概述text_pattern_ops的函式嘗試使用運算符類使用單獨的 btree 索引：f_unaccent()

CREATE INDEX a_name_pattern_idx ON a (f_unaccent(lower(name)) text_pattern_ops);

更多的：

• 在 PostgreSQL 中使用 LIKE、SIMILAR TO 或正則表達式進行模式匹配

然後：

• 重寫WHERE條件以匹配索引表達式
• 使表達式右側的萬用字元顯式以使其適用於準備好的語句。
• 不要將文本參數連接到您的查詢中，這會導致偷偷摸摸的錯誤和SQL 注入。使用準備好的語句：

$sql = 'SELECT a.name, ST_AsText(a.geom), b.name AS name_b, b.id
       FROM   a
       LEFT   JOIN b ON b.id = a.b_id
       WHERE  f_unaccent(lower(a.name)) LIKE (f_unaccent(lower($1)) || $$%$$)
       ORDER  BY a.geom <-> ST_GeomFromText($$POINT($2 $3)$$, 4326)
       LIMIT  2';

$result = pg_query_params($sql, array($searched, $lat, $lon));

我使用美元引用來避免轉義單引號。

對於重複呼叫，使用pg_prepareand pg_execute。細節：

• 我嘗試實施 UPSERT 的問題

或者您可以嘗試使用 GiST 索引 - 結合 PostGis 空間 GiST 索引和功能三元組 GiST 索引的多列、功能性 GiST 索引。我還沒有測試這種組合。

您需要先安裝pg_trgm。指示：

• LIKE 是如何實現的？

CREATE INDEX a_special_gist_idx ON a
USING gist (f_unaccent(lower(name)) gist_trgm_ops, geom);

我把它f_unaccent(lower(name))放在第一位是因為，根據文件：

多列 GiST 索引可以與涉及索引列的任何子集的查詢條件一起使用。附加列上的條件限制了索引返回的條目，但第一列上的條件是確定需要掃描多少索引的最重要條件。如果 GiST 索引的第一列只有幾個不同的值，即使附加列中有許多不同的值，它也會相對無效。

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