由於行估計非常不准確，全文搜尋緩慢
針對該數據庫的全文查詢（儲存 RT ( Request Tracker ) 票證）似乎需要很長時間才能執行。附件表（包含全文數據）約為 15GB。
數據庫模式如下，大約有 200 萬行：
rt4=# \d+ 附件 表“public.attachments” 專欄 | 類型 | 修飾符 | 儲存 | 描述 -----------------+-----------------------------+----------------------------------------------------------+----------+------------- 編號 | 整數 | 不為空預設 nextval('attachments_id_seq'::regclass) | 平原 | 事務ID | 整數 | 不為空 | 平原 | 家長 | 整數 | 不為空 預設 0 | 平原 | 消息ID | 字元變化(160) | | 擴展 | 主題 | 字元變化(255) | | 擴展 | 文件名 | 字元變化(255) | | 擴展 | 內容類型 | 字元變化(80) | | 擴展 | 內容編碼 | 字元變化(80) | | 擴展 | 內容 | 正文 | | 擴展 | 標題 | 正文 | | 擴展 | 創作者 | 整數 | 不為空 預設 0 | 平原 | 創建 | 沒有時區的時間戳| | 平原 | 內容索引 | 向量 | | 擴展 | 索引： "attachments_pkey" 主鍵，btree (id) "attachments1" btree (父) "attachments2" btree (transactionid) “attachments3” btree（父級，transactionid） “contentindex_idx”杜松子酒（contentindex） 有 OID：沒有我可以使用以下查詢非常快速（<1s）自行查詢數據庫：
select objectid from attachments join transactions on attachments.transactionid = transactions.id where contentindex @@ to_tsquery('frobnicate');但是，當 RT 執行一個應該對同一個表執行全文索引搜尋的查詢時，通常需要數百秒才能完成。查詢分析輸出如下：
詢問
SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Transactions Transactions_1 ON ( Transactions_1.ObjectType = 'RT::Ticket' ) AND ( Transactions_1.ObjectId = main.id ) JOIN Attachments Attachments_2 ON ( Attachments_2.TransactionId = Transactions_1.id ) WHERE (main.Status != 'deleted') AND ( ( ( Attachments_2.ContentIndex @@ plainto_tsquery('frobnicate') ) ) ) AND (main.Type = 'ticket') AND (main.EffectiveId = main.id);
EXPLAIN ANALYZE輸出查詢計劃 -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 聚合（成本=51210.60..51210.61 行=1 寬度=4）（實際時間=477778.806..477778.806 行=1 循環=1） -> 嵌套循環（成本=0.00..51210.57 行=15 寬度=4）（實際時間=17943.986..477775.174 行=4197 循環=1） -> 嵌套循環（成本=0.00..40643.08 行=6507 寬度=8）（實際時間=8.526..20610.380 行=1714818 循環=1） -> 門票主序列掃描（成本=0.00..9818.37 行=598 寬度=8）（實際時間=0.008..256.042 行=96990 循環=1） 過濾器： (((status)::text 'deleted'::text) AND (id = effectiveid) AND ((type)::text = 'ticket'::text)) -> 在 transactions_1 上使用 transactions1 進行索引掃描（成本=0.00..51.36 行=15 寬度=8）（實際時間=0.102..0.202 行=18 循環=96990） 索引條件：(((objecttype)::text = 'RT::Ticket'::text) AND (objectid = main.id)) -> 對附件附件_2 使用附件2 進行索引掃描（成本=0.00..1.61 行=1 寬度=4）（實際時間=0.266..0.266 行=0 循環=1714818） 索引條件：（transactionid = transactions_1.id） 過濾器: (contentindex @@ plainto_tsquery('frobncate'::text)) 總執行時間：477778.883 毫秒據我所知，問題似乎在於它沒有使用在
contentindex欄位 (contentindex_idx) 上創建的索引，而是對附件表中的大量匹配行進行過濾。解釋輸出中的行數似乎也非常不准確，即使在最近的ANALYZE：估計行=6507 實際行=1714818 之後也是如此。我不確定下一步該去哪裡。
這可以通過一千零一種方式改進，那麼它應該是毫秒的問題。
更好的查詢
這只是您使用別名重新格式化的查詢，並刪除了一些噪音以清除迷霧：
SELECT count(DISTINCT t.id) FROM tickets t JOIN transactions tr ON tr.objectid = t.id JOIN attachments a ON a.transactionid = tr.id WHERE t.status <> 'deleted' AND t.type = 'ticket' AND t.effectiveid = t.id AND tr.objecttype = 'RT::Ticket' AND a.contentindex @@ plainto_tsquery('frobnicate');您的查詢的大部分問題在於前兩個表
tickets和transactions，問題中缺少這些表。我正在填寫有根據的猜測。
t.status,t.objecttype並且tr.objecttype可能不應該是text, 但enum或者可能是引用查找表的一些非常小的值。
EXISTS半連接假設
tickets.id是主鍵，這種重寫形式應該便宜得多：SELECT count(*) FROM tickets t WHERE status <> 'deleted' AND type = 'ticket' AND effectiveid = id AND EXISTS ( SELECT 1 FROM transactions tr JOIN attachments a ON a.transactionid = tr.id WHERE tr.objectid = t.id AND tr.objecttype = 'RT::Ticket' AND a.contentindex @@ plainto_tsquery('frobnicate') );不要將行與兩個 1:n 連接相乘，而只是在最後折疊多個匹配項，而是
count(DISTINCT id)使用EXISTS半連接，一旦找到第一個匹配項就可以停止進一步查找，同時廢棄最後DISTINCT一步。根據文件：子查詢通常只會執行足夠長的時間來確定是否至少返回了一行，而不是一直到完成。
有效性取決於每張票的交易數量和每筆交易的附件數量。
確定連接順序
join_collapse_limit如果您知道您的搜尋詞
attachments.contentindex是非常有選擇性的 - 比查詢中的其他條件更具選擇性（這可能是 ‘frobnicate’ 的情況，但不是 ‘problem’），您可以強制連接順序。查詢規劃器很難判斷特定詞的選擇性，除了最常見的詞。根據文件：
join_collapse_limit(integer)$$ … $$
因為查詢計劃器並不總是選擇最佳連接順序，高級使用者可以選擇暫時將此變數設置為 1，然後明確指定他們想要的連接順序。
用於
SET LOCAL僅為目前事務設置它的目的。BEGIN; SET LOCAL join_collapse_limit = 1; SELECT count(DISTINCT t.id) FROM attachments a -- 1st JOIN transactions tr ON tr.id = a.transactionid -- 2nd JOIN tickets t ON t.id = tr.objectid -- 3rd WHERE t.status <> 'deleted' AND t.type = 'ticket' AND t.effectiveid = t.id AND tr.objecttype = 'RT::Ticket' AND a.contentindex @@ plainto_tsquery('frobnicate'); ROLLBACK; -- or COMMIT;
WHERE條件的順序總是無關緊要的。只有連接的順序在這裡是相關的。或者使用像@jjanes 在“選項 2”中解釋的 CTE。以獲得類似的效果。
索引
B-Tree索引
tickets採用與大多數查詢相同的所有條件，並在上創建部分索引****tickets：CREATE INDEX tickets_partial_idx ON tickets(id) WHERE status <> 'deleted' AND type = 'ticket' AND effectiveid = id;如果其中一個條件是可變的，則將其從條件中刪除，
WHERE並將該列作為索引列添加。另一個關於**
transactions**：CREATE INDEX transactions_partial_idx ON transactions(objecttype, objectid, id)第三列僅用於啟用僅索引掃描。
此外，由於您有此復合索引，其中包含兩個整數列**
attachments**："attachments3" btree (parent, transactionid)這個額外的索引完全是浪費，刪除它：
"attachments1" btree (parent)細節：
GIN指數
添加
transactionid到您的 GIN 索引中以使其更有效。這可能是另一個靈丹妙藥，因為它可能允許僅索引掃描，完全消除對大表的訪問。您需要附加模組提供的附加運算符類
btree_gin。詳細說明："contentindex_idx" gin (**transactionid,** contentindex)一列中的 4 個字節
integer不會使索引變得更大。此外，幸運的是，GIN 索引在一個關鍵方面與 B-tree 索引不同。根據文件：多列 GIN 索引可以與涉及 索引列的任何子集的查詢條件一起使用。與 B-tree 或 GiST 不同，無論查詢條件使用哪個索引列，索引搜尋的有效性都是相同的。
大膽強調我的。因此，您只需要一個（大且有些昂貴的）GIN 索引。
表定義
將
integer not null columns移到前面。這對儲存和性能有一些小的積極影響。在這種情況下，每行節省 4 - 8 個字節。Table "public.attachments" Column | Type | Modifiers -----------------+-----------------------------+------------------------------ id | integer | not null default nextval('... transactionid | integer | not null parent | integer | not null default 0 creator | integer | not null default 0 -- ! created | timestamp | -- ! messageid | character varying(160) | subject | character varying(255) | filename | character varying(255) | contenttype | character varying(80) | contentencoding | character varying(80) | content | text | headers | text | contentindex | tsvector |