Postgresql

查詢 ANY(列)時不搜尋 GIN 索引

  • March 2, 2022

在 Postgres 9.4.11 數據庫中,我有一個很大的書籍表,它與作者是一對多的關係。理論上為了提高性能,我已經在電子書記錄中記憶體了作者姓名以便快速搜尋。列的屬性authors_cache

authors_cache 的螢幕截圖

範例行:

樣本行

我在此列上創建了 GIN 索引:

CREATE  INDEX  "index_ebooks_on_authors_cache" ON "ebooks" USING gin ("authors_cache")

它在搜尋時不使用索引authors_cache並且花費了不可接受的時間:

EXPLAIN ANALYZE
SELECT  * FROM "ebooks"
WHERE ('Charles Bukowski' = ANY (authors_cache))
LIMIT 60 OFFSET 0;

結果:

Limit  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.962..10067.070 rows=59 loops=1)
 ->  Seq Scan on ebooks  (cost=0.00..172334.67 rows=30 width=124) (actual time=71.960..10067.015 rows=59 loops=1)
       Filter: ('Charles Bukowski'::text = ANY ((authors_cache)::text[]))
       Rows Removed by Filter: 894504
Planning time: 0.188 ms
Execution time: 10067.112 ms

10 秒是不可接受的時間量。我對設計更改持開放態度,因為我還沒有“嫁給”這個專欄。

使用GIN 索引支持的**數組運算符。**構造= ANY ()不是。

SELECT *
FROM   ebooks
WHERE  authors_cache @> '{Charles Bukowski}'  -- array literal
LIMIT  60;

詳細解釋:

不確定您的去規範化是否會為您帶來很多收益。具有簡單 btree 索引的標準 1:n 實現也應該很好地執行。

如果您使用這種方法,我會考慮’sint[])author_id顯著較小的數組列和 GIN 索引。參照完整性的潛在問題大致相同。(數組元素目前不能有 FK 約束。到目前為止,在 Postgres 中實現這一點的嘗試沒有成功。)

哦,考慮升級。自 Postgres 9.4 以來,使用 GIN 索引的性能得到了顯著改善。

引用自:https://dba.stackexchange.com/questions/187959