有沒有辦法可以加快針對此特定視圖的查詢？

我在 PostgreSQL 數據庫中有三個表，我通過視圖和一些連接進行查詢。

CREATE TABLE network_info (
 network         CIDR          NOT NULL,
 some_info       TEXT          NULL,
 PRIMARY KEY (network)
);

CREATE TABLE ipaddr_info (
 ipaddr          INET          NOT NULL,
 some_info       INT           NULL,
 PRIMARY KEY (ipaddr, some_info)
);

CREATE TABLE ipaddrs (
 addr            INET          NOT NULL,
 PRIMARY KEY (addr)
);

CREATE VIEW ipaddr_summary AS
SELECT DISTINCT
 i.addr                  AS ip_address,
 a.some_info             AS network_info,
 COUNT(b.ipaddr)         AS ip_info_count
FROM ipaddrs AS i
LEFT JOIN network_info AS a
 ON (i.addr << a.network)
LEFT JOIN ipaddr_info AS b
 ON (i.addr = b.ipaddr)
GROUP BY i.addr, a.some_info
;

SELECT * from ipaddr_summary;所有的表現在都有大約 150K 行，在執行 PostgreSQL 9.3 的 Intel Pentium 4 2.8GHz 雙核和 2G 記憶體上執行需要很長時間（大約 3 小時） 。

有沒有辦法可以重組或優化這個特定的模式或視圖以使查詢更快，或者它是硬體問題？我將在雲中啟動一個強大的 VM 並進行測試，但想看看是否有一種方法可以優化而無需向其投入更多硬體。

也可能存在硬體問題 - 我們應該怎麼知道？但是查詢肯定存在問題。

首先，DISTINCT從您的VIEW定義中刪除。它什麼都不做*（*但使事情複雜化和減慢速度）。關於 SO 的相關答案和解釋：

• PostgreSQL - 在 VIEW 上加入慢查詢

到達這個（清理後的）查詢：

SELECT i.addr      AS ip_address
    , a.some_info AS network_info
    , COUNT(b.ipaddr) AS ip_info_count
FROM   ipaddrs           i
LEFT   JOIN ipaddr_info  b  ON i.addr = b.ipaddr
LEFT   JOIN network_info a  ON i.addr << a.network
GROUP  BY 1,2;

接下來，查詢看起來很可疑，好像出錯了。兩個不相關的連接可以乘以行：

• 兩個 SQL LEFT JOINS 產生不正確的結果

每個表中有 150k 行，有可能產生巨大的（非預期的）笛卡爾積。我有根據的猜測，你真的想要這個：

SELECT addr        AS ip_address
    , a.some_info AS network_info
    , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
  SELECT  ipaddr AS addr, count(*) AS ip_info_count
  FROM    ipaddr_info
  GROUP   BY 1
 ) b USING (addr)
LEFT   JOIN network_info a ON i.addr << a.network

我懷疑現在GROUP BY外部也不需要SELECT。除了固定計數之外，這也可能會快幾個數量級，從而避免代理交叉連接。

您可以先加入ipaddrs（特別是如果您有從中過濾行的謂詞）然後聚合，或者首先在子查詢中聚合，如顯示的那樣。這種變體的有用性很大程度上取決於數據分佈。這對少數 ipaddr人來說非常有用。細節：

• 與使用聚合的子查詢相關的慢查詢

最後，您需要索引支持。ipaddr和之間的相等性addr被PRIMARY KEY. 無論如何，對整個表的查詢可能都在使用順序掃描。

對於“被包含”運算<<符運算符，您需要一個 GIN 或 GiST 索引。最好的選擇是Postgres 9.4中的新**inet_ops**GiST 索引運算符類（支持數據類型和）：inet``cidr

CREATE INDEX ON network_info USING gist (network inet_ops);

不確定索引是否可以在普通INNER（或OUTER）連接中使用。目前無法測試。也許您需要相關子查詢或LATERAL連接來利用索引：

SELECT addr AS ip_address
    , a.network_info
    , b.ip_info_count
FROM   ipaddrs i
LEFT   JOIN (
  SELECT  ipaddr AS addr, count(*) AS ip_info_count
  FROM    ipaddr_info
  GROUP   BY 1
 ) b USING (addr)
LEFT   JOIN LATERAL (
  SELECT some_info AS network_info
  FROM   network_info
  WHERE  network >> i.addr
  ) a ON TRUE;

在舊版本中建立索引的建議：

• IPv4 地址的 PostgreSQL 欄位數據類型

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