在 Postgres 中向系統目錄添加索引

我的情況與此處描述的情況非常相似：

我有一個 SaaS 情況，我在單個數據庫中使用 1000 多個模式（每個模式包含相同的表，每個租戶只是不同的數據）。我使用了模式，以便共享的應用程序伺服器可以為所有模式共享到單個數據庫的連接。一切正常。

而且，雖然應用程序本身看起來執行良好，但一些涉及系統目錄的查詢卻非常慢。此外，psql的自動完成完全沒用而且\dt非常慢。

特別是，我需要使用以下方法計算每個模式的磁碟大小：

SELECT sum(pg_total_relation_size(c.oid)) AS size
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = 'abbiecarmer'
AND    c.relkind = 'r';

這很慢。查看查詢計劃，我看到​​了

Aggregate  (cost=136903.16..136903.17 rows=1 width=4) (actual time=1024.420..1024.420 rows=1 loops=1)             
 ->  Hash Join  (cost=8.28..136902.86 rows=59 width=4) (actual time=143.247..1016.749 rows=60 loops=1)           
       Hash Cond: (c.relnamespace = n.oid)                                                                       
       ->  Seq Scan on pg_class c  (cost=0.00..133645.24 rows=866333 width=8) (actual time=0.045..943.029 rows=879788 loops=1)                                                                                                    │
             Filter: (relkind = 'r'::"char")                                                                     
             Rows Removed by Filter: 2610112                                                                     
       ->  Hash  (cost=8.27..8.27 rows=1 width=4) (actual time=0.032..0.032 rows=1 loops=1)                      
             Buckets: 1024  Batches: 1  Memory Usage: 1kB                                                        
             ->  Index Scan using pg_namespace_nspname_index on pg_namespace n  (cost=0.00..8.27 rows=1 width=4)(actual time=0.029..0.030 rows=1 loops=1)                                                                        │
                   Index Cond: (nspname = 'abbiecarmer'::name)                                                   
Total runtime: 1024.476 ms                                                                                        

如果我理解正確的話，這表明 90% 的查詢時間用於順序掃描pg_class關係。

我停止了 postmaster，以單使用者模式執行後端並添加了以下索引：

create index pg_class_relnamespace_index on pg_class(relnamespace);
REINDEX INDEX pg_class_relnamespace_index;

create index pg_class_reltablespace_index on pg_class(reltablespace);
REINDEX INDEX pg_class_reltablespace_index;

（我也有數千個表空間）。現在查詢速度快了約 100 倍，並且計劃看起來更好：

Aggregate  (cost=846.91..846.92 rows=1 width=4) (actual time=10.609..10.610 rows=1 loops=1)                       
 ->  Nested Loop  (cost=0.00..846.61 rows=60 width=4) (actual time=0.069..0.320 rows=60 loops=1)                 
       ->  Index Scan using pg_namespace_nspname_index on pg_namespace n  (cost=0.00..8.27 rows=1 width=4) (actual time=0.023..0.024 rows=1 loops=1)                                                                              │
             Index Cond: (nspname = 'abbiecarmer'::name)                                                        
       ->  Index Scan using pg_class_relnamespace_index on pg_class c  (cost=0.00..837.59 rows=75 width=8) (actual time=0.043..0.271 rows=60 loops=1)                                                                             │
             Index Cond: (relnamespace = n.oid)                                                                 
             Filter: (relkind = 'r'::"char")                                                                    
             Rows Removed by Filter: 102                                                                        
Total runtime: 10.696 ms                                                                                         

然而，在上面的文章中，作為 Postgres 核心貢獻者之一的 Tom Lane 說：

這裡有很多陷阱，特別是您創建索引的會話不會知道它在那裡（因此在這種情況下，之後可能建議對 pg_class 進行重新索引）。 我仍然認為在生產數據庫上嘗試它會很瘋狂，但是……

我還擔心在 Postgres 9.0 和 9.1（我使用的是 9.2）中似乎完全禁用了對系統目錄的修改——我想這樣做是有原因的？

**所以，問題是：**在 Postgres 中向系統目錄添加索引有什麼問題，如果我（最終）在生產系統上這樣做，我會不會發瘋？

畢竟，診斷並不是那麼離題。顯然，目錄並沒有準備好處理成千上萬的模式。

不幸的是，您正在嘗試使用資訊模式中的視圖，這可能會非常緩慢。這些是涉及許多表的複雜視圖，以產生完全符合標準的狀態。只需查看EXPLAIN ANALYZEpgAdmin 的輸出或圖形表示即可獲得印象。

直接使用目錄表：

而且，雖然項目沒有保證，但基本元素pg_namespace或pg_class不太可能在主要版本之間發生變化。

試試這個查詢。開箱即用應該更快：

SELECT pg_total_relation_size(c.oid) AS size
FROM   pg_namespace n
JOIN   pg_class     c ON c.relnamespace = n.oid
WHERE  n.nspname = :schema_name
AND    c.relkind = 'r';

或者可能更快一點：

SELECT pg_total_relation_size(c.oid) AS size
FROM   pg_class c 
WHERE  c.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = :schema_name)
AND    c.relkind = 'r';

-> SQLfiddle 展示（包括更新）

如果要使用索引，請在部分索引pg_class上創建第一個：

CREATE INDEX pg_class_relnamespace_idx on pg_class(relnamespace)
WHERE relkind = 'r';

更小，更快，不太可能導致問題，因為索引本身不包含在索引中。

您在問題中的第二個索引可能是複制/粘貼工件。您沒有提到表空間發揮作用，您的查詢或查詢計劃也沒有顯示任何與之相關的內容。

create index pg_class_reltablespace_index on pg_class(reltablespace);

必須是：

CREATE INDEX pg_namespace_nspname_idx on pg_namespace(nspname);

但是，我當然不會聲稱對系統目錄的了解幾乎與 Tom Lane 一樣多。如果他說你會瘋狂地在一個高效的系統中嘗試這個，那麼無論如何你都會瘋狂地去做。

湯姆又一次寫道：

我仍然認為在生產數據庫上嘗試它會很瘋狂，但是……

大膽強調我的。這告訴我，他並不完全反對，只是不願意為任何事情做保證。這使它不那麼瘋狂。我仍然不會推薦它。

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