PostgreSQL NOT IN 數組慢查詢

我有一張有數百萬行的大桌子。每行都有一個數組欄位tags。我也有正確的 GIN 索引tags。

計算有標籤的行很快（~7s）：

SELECT COUNT(*) FROM "subscriptions" WHERE (tags @> ARRAY['t1']::varchar[]);

但是計算沒有標籤的行非常慢（~70s）：

SELECT COUNT(*) FROM "subscriptions" WHERE NOT (tags @> ARRAY['t1']::varchar[]);

我也嘗試了其他變體，但結果相同（約 70 秒）：

SELECT COUNT(*) FROM "subscriptions" WHERE NOT ('t1' = ANY (tags));

如何使“不在數組中”操作快速？

感謝pgsql-performance 郵件列表中的Jeff Janes，我已經解決了：

PostgreSQL 沒有將 GIN 索引用於“NOT”操作。在整個數組上創建 Btree 索引解決了這個問題，允許只掃描索引。現在查詢只需要幾毫秒而不是幾分鐘。

如果 ’t1’ 是一個罕見的標籤，計算沒有標籤的行會導致計算大部分“數百萬行”。即使 ’t1’ 很常見，從索引中計算超過百分之幾的行也不會比順序掃描有任何改進。不管怎樣，這永遠不會很快。索引不會有幫助。

如果您必須進行幾次計數，不包括稀有標籤 - 同時總行數沒有變化（或最小變化無關緊要），可能的優化是獲得一次總行數（慢）和用標籤減去（小）行數（快速匹配索引）……

根據確切的要求和您的完整案例，可能還有其他快捷方式。看：

• 在 PostgreSQL 中發現表的行數的快速方法

最重要的是，索引通常只能幫助辨識相對較小百分比的表行。順便說一句，IN和= ANY()包含操作符@>是相關的工具，但有細微的差別。GIN 索引通常只支持正確的數組運算符。看：

• PostgreSQL 可以索引數組列嗎？

您可以通過將整數數組與運算符和基於附加intarray模組提供的運算符類的索引結合使用來獲得一些東西。高度優化，但不能違背上述原則。

然後，您還可以像在表達式any mixture of tags that the row must have or must not have中評論一樣組合。query_int

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