可以使用第一個 CTE 作為第二個 CTE 的過濾器嗎？

我正在嘗試優化大型數據庫上的查詢，該數據庫基本上為網站獲取 50 張圖像以及它們出現的頁面的 URL。目前查詢適用於較小的站點但較大的站點，例如我正在測試的具有 50,000 張圖像的站點，查詢實際上超時。

我已將慢速範圍縮小到第二個 CTE (y)，因為它正在獲取該表中包含超過 150M 行的所有匹配記錄。我想做的是使用第一個 CTE (x) 作為第二個 CTE 的輸入，這樣只有與在 x 中獲取的資源相關的記錄才會在 y 中獲取，因為 x 是發生過濾的地方，結果限制為 50 個詢問。這可能嗎？

WITH x AS 
(
   SELECT vi.resourceid
   FROM vw_image vi
   WHERE vi.siteid = 2294
   AND vi.childtype = 'i'
   ORDER BY vi.id
   OFFSET 1 ROWS
   FETCH NEXT 50 ROWS ONLY
),
y AS (
   SELECT rop.resourceid as resourceid, r.url as url
   FROM resourceonpage rop
   INNER JOIN resource r ON rop.pageid = r.id
   WHERE rop.siteid = 2294
)
SELECT i.*, string_agg(y.url, ',') as urllist
FROM x 
INNER JOIN vw_image i ON x.resourceid = i.resourceid
LEFT JOIN y ON y.resourceid = x.resourceid;

看著我的水晶球，您的查詢可能會以這樣的數量級更快地工作：

SELECT i.*, rr.urllist
FROM   vw_image i
LEFT   JOIN LATERAL (
  SELECT string_agg(r.url, ',') AS urllist
  FROM   resourceonpage rop
  JOIN   resource r ON r.id = rop.pageid
  WHERE  rop.siteid   = 2294  -- or: = i.siteid
  AND    r.resourceid = i.resourceid
  ) rr ON true
WHERE  i.siteid = 2294
AND    i.childtype = 'i'
ORDER  BY i.id
OFFSET 1 ROWS
FETCH  NEXT 50 ROWS ONLY;  -- or just: OFFSET 1 LIMIT 50

你在這裡不需要 CTE，那些只會減慢你的速度。

我使用LATERAL連接在子查詢上使用索引resourceonpage並resource立即在子查詢中聚合。關於LEFT JOIN LATERAL ... ON true：

• LATERAL 和 PostgreSQL 中的子查詢有什麼區別？

更深入地研究我的水晶球，最好的指標可能是：

CREATE INDEX ON vw_image (siteid, id) WHERE childtype = 'i';
CREATE INDEX ON resourceonpage (siteid, pageid);
CREATE INDEX ON resource (resourceid, id, url);

url僅當您從中獲得僅索引掃描時，附加才有用。

另一種方法是在FROM列表中使用括號，如下所示：

• 如何在時間跨度內包含多個分組的缺失數據？

但是，根據我的水晶球（和有根據的猜測），LATERAL版本會更快。問題中沒有足夠的資訊來給您提供有根據的猜測。

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