問題

如何改進此查詢以將如下所示的查詢速度從30+ 秒提高到毫秒？我正在使用PostgreSQL: v 9.6.6。

語境

我有一個時間序列表buildings.hispoint，用於儲存表中數據點的歷史數據buildings.point。

我需要匯總Max(value) - Min(value) as aggregation_value大量數據點的不同時間範圍（例如年初至今）。

這被證明是非常緩慢的，需要改進。

**（20,210,129 行）**的表結構buildings.hispoint

CREATE TABLE buildings.hispoint (
   id int,
   value float,
   datetime timestamp,
   point_id …
   CONSTRAINT foo FOREIGN KEY (point_id)
   REFERENCES buildings.point (point_id),
   …
);

詢問

SELECT COUNT(datetime) AS id, 
MAX(value) - MIN(value) AS aggregation_value
FROM buildings_hispoint
WHERE point_id = 44 
AND buildings_hispoint.datetime BETWEEN '2018-01-01 00:00:00' AND '2018-05-02 09:18:14';

查詢計劃

Aggregate  (cost=160967.11..160967.12 rows=1 width=16) (actual time=21713.720..21713.720 rows=1 loops=1)
 Buffers: shared hit=7817 read=66145 dirtied=1
 ->  Bitmap Heap Scan on buildings_hispoint  (cost=5705.82..159708.13 rows=167864 width=16) (actual time=161.733..21585.478 rows=19783 loops=1)
       Recheck Cond: (point_id = 44)
       Rows Removed by Index Recheck: 3474664
       Filter: ((datetime >= '2018-01-01 00:00:00+00'::timestamp with time zone) AND (datetime <= '2018-05-02 09:18:14+00'::timestamp with time zone))
       Rows Removed by Filter: 306305
       Heap Blocks: exact=46580 lossy=26488
       Buffers: shared hit=7817 read=66145 dirtied=1
       ->  Bitmap Index Scan on buildings_measurementdatapoint_ffb10c68  (cost=0.00..5663.85 rows=306589 width=0) (actual time=139.360..139.360 rows=326088 loops=1)
             Index Cond: (point_id = 44)
             Buffers: shared read=894
Planning time: 40.504 ms
Execution time: 21717.750 ms

上的索引(point_id, datetime, value)可能會加快查詢速度，因為它只需要進行索引查找，只讀取索引的相關部分，並且還可以（從索引中）獲得value列的所有值來計算MINand MAX。

(point_id)(A)或 (B)上的索引(point_id, datetime)還需要從表中找到讀取的相關元組，以便 (A) 針對日期時間範圍過濾它們，以及 (A 和 B) 讀取value列的值。

你要在執行時浮動，

MAX(value::float) - MIN(value::float) AS aggregation_value

value如果您想要那種效果，只需將類型更改為浮動即可。

ALTER TABLE buildings.hispoint
 ALTER COLUMN value
 SET DATA TYPE TO float;

另外，ORDER BY 1 asc如果您只返回一行，那又有什麼意義呢？

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