Postgresql
查詢 ANY(列)時不搜尋 GIN 索引
在 Postgres 9.4.11 數據庫中,我有一個很大的書籍表,它與作者是一對多的關係。理論上為了提高性能,我已經在電子書記錄中記憶體了作者姓名以便快速搜尋。列的屬性
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 實現也應該很好地執行。
如果您使用這種方法,我會考慮’s
int[]
)author_id
顯著較小的數組列和 GIN 索引。參照完整性的潛在問題大致相同。(數組元素目前不能有 FK 約束。到目前為止,在 Postgres 中實現這一點的嘗試沒有成功。)哦,考慮升級。自 Postgres 9.4 以來,使用 GIN 索引的性能得到了顯著改善。