具有大 IN 和數百萬條記錄的優化選擇結果

我使用一個包含 1.5B 記錄的表。

數據庫：RDS、PostgreSQL 12.4、16GB RAM、4vCPU

架構：

CREATE TABLE public.trip (
    id bigint NOT NULL,
    cell_to character varying NOT NULL,
    cell_from character varying NOT NULL,
    indicator character varying NOT NULL,
    time_id integer,
    weight double precision
);

CREATE INDEX ix_trip_cell_from ON public.trip USING btree (cell_from);
CREATE INDEX ix_trip_cell_to ON public.trip USING btree (cell_to);
CREATE INDEX ix_trip_indicator ON public.trip USING btree (indicator);
CREATE INDEX ix_trip_time_id ON public.trip USING btree (time_id);

我正在嘗試提取在某些單元格內發生的所有行程（輸出大約 7-12 百萬條記錄）：

EXPLAIN ANALYZE SELECT
   cell_to,
   cell_from,
   time_id,
   weight AS trips
FROM
   trip
WHERE
   cell_to IN (VALUES ... 1k values)
 AND 
   cell_from IN (VALUES ... 1k values (the same as above))
 AND
   time_id IN (VALUES ... 3 to 20 values)
 AND
   indicator = 'some string';

你可以在這裡找到結果https://explain.depesz.com/s/RxH4。

我試過的：

1. 替換為 INNER JOIN -> 得到了一些改進
2. 將 b-tree 索引更改為 BRIN -> 稍微改進了時序
3. VACUUM、REINDEX、work_mem -> 零效應

查詢仍然執行時間過長。

更新：

感謝@NikitaSerbskiy 和@Laurenz Albe，強制postgresql 使用索引並添加多列索引有很大幫助。

如果您增加到work_mem200MB 或更多，您可能會獲得點陣圖索引掃描和更好的性能，以便您的表的點陣圖適合它。

除此之外，我能看到的唯一補救措施是通過提高max_parallel_workers_per_gather.

但是，如果您計劃在這台微型機器上執行多個並發查詢，那麼所有這些優化都是有問題的。

更新：

實驗enable_seqscan = off表明 PostgreSQL 高估了索引掃描的成本。

因此，如果您降低random_page_cost到接近 1 的值，PostgreSQL 應該會自動選擇更好的計劃。

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