PostgreSQL：性能：按索引列排序會降低查詢性能

SELECT s.id, s.name, s.updated_at, COUNT(*) OVER ()
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'
WHERE 1=1 ORDER BY s.name asc  LIMIT 50 OFFSET 0

這是查詢計劃： https ://explain.depesz.com/s/UC9B

這是關聯表中的索引

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);

我刪除ORDER BY s.name查詢的那一刻非常快。但是，s.name被索引了，為什麼會這樣呢？有沒有辦法優化查詢？請建議。

我需要按名稱排序的結果。我可以注意到刪除COUNT(*) OVER()大大提高了查詢執行時間。但我也需要COUNT。

檢索排序結果需要更多努力是正常的。

如果您想要總計數，則必須掃描所有行，即使您只返回前 50 行。

您可以嘗試order by name || ''查看排序是否會更快。

我的建議是不要與查詢一起檢索總計數。

在這種情況下，優化器的估計值很好，因此您應該通過執行來使用近似結果計數

EXPLAIN (FORMAT JSON) SELECT ...

並檢索Plan Rows.

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