Postgres：性能：慢速索引掃描

我有一個非常大的查詢，它從各種表中查詢數據。我嘗試了各種方法來優化查詢。

• LEFT JOIN使as 成為可能LEFT LATERAL JOIN，以便在從主查詢獲取結果後計算橫向連接查詢
• GROUP BY由於聚合需要更多時間，因此已刪除。

但是，當我嘗試按索引列對其進行排序時，即使是我提出的最終查詢也很慢。請向我建議我應該如何開始優化。

這是查詢和解釋分析詳細資訊 https://explain.depesz.com/s/kTBh

這是索引掃描打開的解釋（分析，緩衝區）： https ://explain.depesz.com/s/SyRu

這是關閉索引掃描的解釋（分析，緩衝區）： https ://explain.depesz.com/s/TPDv

表上的索引：

CREATE INDEX idx_screens_name ON public.screens USING btree (name);
CREATE INDEX idx_screens_number ON public.screens USING btree (((data ->> 'number'::text)));
CREATE INDEX idx_screens_tags ON public.screens USING gin (tags);
CREATE INDEX idx_screens_theatre_id ON public.screens USING btree (theatre_id);

CREATE INDEX idx_theatre_companies ON public.theatres USING gin (company_ids);
CREATE INDEX idx_theatres_chain_name ON public.theatres USING btree ((((data -> 'chain'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_city_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'city'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_country_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'country'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_name ON public.theatres USING btree (name);
CREATE INDEX idx_theatres_province_name ON public.theatres USING btree (((((data -> 'address'::text) -> 'province'::text) ->> 'name'::text)));
CREATE INDEX idx_theatres_qwc_is_active ON public.theatres USING btree (qwc_is_active);
CREATE INDEX idx_theatres_tags ON public.theatres USING gin (tags);

CREATE UNIQUE INDEX company_screen_mappings_key ON public.company_screen_mappings USING btree (company_id, screen_id) WHERE (is_deleted = false);

CREATE UNIQUE INDEX idx_scheduling_times_company_id_screen_id_content_type_id ON public.scheduling_times USING btree (company_id, screen_id, content_type_id);
CREATE INDEX idx_screen_right_mappings_date_range ON public.screen_right_mappings USING gist (daterange(valid_from, valid_till));
CREATE INDEX idx_screen_right_mappings_time_range ON public.screen_right_mappings USING gist (public.tmrange(start_time, end_time));

查看查詢：您的大多數LEFT JOINs 並不是真正LEFT JOIN的 s，因為謂詞ON TRUE使它們表現為CROSS JOINs。您的查詢正在考慮來自以下各項的每一行：

FROM screens s
INNER JOIN theatres t ON s.theatre_id = t.id 
LEFT JOIN company_screen_mappings AS csm ON csm.screen_id = s.id AND csm.is_deleted = FALSE AND csm.company_id = 'f5003aa3-3621-498a-ab0f-526706fda88f'

針對從被稱為, , , , , 和的子查詢中出來的每個行組合。如果任何或所有這些部分返回大量行，這將非常耗時。由於需要將所有內容與幾乎沒有或沒有約束謂詞的所有內容進行比較，因此掃描是不可避免的（它需要讀取所有內容來進行比較）。r``p``c``ste``activation``rc

對我來說，這看起來像是一個GROUP BY存在可以嘗試解決的邏輯缺陷（很像通過添加來修復意外笛卡爾乘積的常見反模式DISTINCT），此外，它看起來像是一個由 ORM 生成的查詢，它已經被賦予了一些不正確的加入選項，雖然不知道應用程序或查詢試圖做什麼，但我當然不能肯定地知道這些事情中的任何一個。

對於在這種情況下如何進行：首先我會要求開發人員查看應用程序的要求（如果它正在通過 ORM 工作），可能需要在那裡進行修復或簡化。如果這是一個手寫查詢（我懷疑它會像這樣一次性編寫，但隨著越來越多的功能被添加到它支持的報告/螢幕中，它可能會隨著時間的推移而增長）而不是生成 ORM，那麼我的首先想到的是返回規範並從頭開始將其重寫為這些要求。

看起來大部分時間都歸結為一個節點，它可以（可能：不太確定 LIMIT 將如何在這裡互動）通過簡單的查詢來建模：

select * from screens order by name

你能顯示EXPLAIN (ANALYZE, BUFFERS)那個查詢，enable_indexscan 設置為 on 和 off 嗎？請先開啟 track_io_timing。如果您也EXPLAIN (ANALYZE, BUFFERS)為原始查詢也這樣做，那也很好，因為這會提供很多有用的資訊。

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