對索引時間戳列進行排序時，PosgreSQL 查詢速度很慢

我有以下問題：下表：

   CREATE TABLE public.current_read
(
   id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
   cube_id bigint NOT NULL,
   end_time timestamp with time zone NOT NULL,
   period_type character varying COLLATE pg_catalog."default" NOT NULL,
   energy_balance double precision NOT NULL,
   created_at timestamp with time zone NOT NULL DEFAULT now(),
   CONSTRAINT current_read_pkey PRIMARY KEY (id),
   CONSTRAINT current_read_cube_id_fkey FOREIGN KEY (cube_id)
       REFERENCES cubemgmt.cube (id) MATCH SIMPLE
       ON UPDATE NO ACTION
       ON DELETE NO ACTION
)
WITH (
   OIDS = FALSE
)
TABLESPACE pg_default;

-- Index: id-current_read-cube_id+end_time+period_type

-- DROP INDEX public."id-current_read-cube_id+end_time+period_type";

CREATE INDEX "id-current_read-cube_id+end_time+period_type"
   ON public.current_read USING btree
   (cube_id, end_time DESC, period_type COLLATE pg_catalog."default")
   TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+end_time

-- DROP INDEX public."ix-current_read-cube_id+end_time";

CREATE INDEX "ix-current_read-cube_id+end_time"
   ON public.current_read USING btree
   (cube_id, end_time DESC)
   TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+period_type

-- DROP INDEX public."ix-current_read-cube_id+period_type";

CREATE INDEX "ix-current_read-cube_id+period_type"
   ON public.current_read USING btree
   (cube_id, period_type COLLATE pg_catalog."default")
   TABLESPACE pg_default;

-- Index: ix-currrent_read-end_time

-- DROP INDEX public."ix-currrent_read-end_time";

CREATE INDEX "ix-currrent_read-end_time"
   ON public.current_read USING btree
   (end_time DESC)
   TABLESPACE pg_default;

ALTER TABLE public.current_read
   CLUSTER ON "ix-currrent_read-end_time";

目前有大約 6000 萬個條目。

當我做一個簡單的查詢時：

SELECT energy_balance 
                 FROM public.current_read
                 WHERE cube_id = 5
                 AND period_type = '1min'
                 ORDER BY end_time desc 
                 LIMIT 120

返回結果大約需要 3-4 秒。如果我刪除 ORDER BY 語句，我會在幾毫秒內得到（隨機）結果。

解釋簡單如下：

"Limit  (cost=730066.65..730080.65 rows=120 width=16) (actual time=1560.745..1613.023 rows=120 loops=1)"
"  ->  Gather Merge  (cost=730066.65..772726.46 rows=365630 width=16) (actual time=1560.743..1613.000 rows=120 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        ->  Sort  (cost=729066.63..729523.67 rows=182815 width=16) (actual time=1538.981..1538.991 rows=101 loops=3)"
"              Sort Key: end_time DESC"
"              Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 0:  Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 1:  Sort Method: top-N heapsort  Memory: 30kB"
"              ->  Parallel Bitmap Heap Scan on current_read  (cost=11257.81..721839.14 rows=182815 width=16) (actual time=432.012..1510.892 rows=138113 loops=3)"
"                    Recheck Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"                    Heap Blocks: exact=120165"
"                    ->  Bitmap Index Scan on "ix-current_read-cube_id+period_type"  (cost=0.00..11148.13 rows=438756 width=0) (actual time=360.098..360.098 rows=414338 loops=1)"
"                          Index Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"Planning Time: 0.297 ms"
"Execution Time: 1613.112 ms"

所以看起來該查詢沒有考慮索引“ix-current_read-end_time”。

知道如何確保使用索引或如何改進查詢時間嗎？

非常感謝，

乾杯

弗里茨

上的索引(cube_id, period_type)不能用於 ORDER BY。但它可以AND period_type = '1min'非常有效地實現。

上的索引(cube_id, end_time, period_type)可用於 ORDER BY，但不能有效地用於AND period_type = '1min'. 它可以使用，只是效率不高。它可以在不訪問表的情況下過濾索引中的行，但它不能跳轉到它知道所有 ‘1min’ 值所在的索引的特定部分。（原因是給定 cube_id 中的所有 ‘1min’ 值都沒有放在一起）

所以，它必須做出選擇，高效的 ORDER BY 或高效period_type = '1min'的 。也許它做出了錯誤的選擇，儘管你還沒有證明這一點。

您應該創建索引(cube_id, period_type, end_time)。那麼它就不必做出選擇，它可以兩全其美。它可以跳轉到索引 where 的部分cube_id = 5 AND period_type = '1min'，一旦到達那裡，它就可以按 end_time 的順序讀取行。

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