当尝试根据某些条件从数据表中提取max(时间戳)时,如果存在匹配的行,则使用MAX()比ORDER BY时间戳LIMIT 1慢,但如果找不到匹配的行,则速度要快得多.
SELECT timestamp FROM data JOIN sensors ON ( sensors.id = data.sensor_id ) WHERE sensor.station_id = 4 ORDER BY timestamp DESC LIMIT 1; (0 rows) Time: 1314.544 ms SELECT timestamp FROM data JOIN sensors ON ( sensors.id = data.sensor_id ) WHERE sensor.station_id = 5 ORDER BY timestamp DESC LIMIT 1; (1 row) Time: 10.890 ms SELECT MAX(timestamp) FROM data JOIN sensors ON ( sensors.id = data.sensor_id ) WHERE sensor.station_id = 4; (0 rows) Time: 0.869 ms SELECT MAX(timestamp) FROM data JOIN sensors ON ( sensors.id = data.sensor_id ) WHERE sensor.station_id = 5; (1 row) Time: 84.087 ms
(timestamp)和(sensor_id,timestamp)上有索引,我注意到Postgres对这两种情况使用了非常不同的查询计划和索引:
QUERY PLAN (ORDER BY) -------------------------------------------------------------------------------------------------------- Limit (cost=0.43..9.47 rows=1 width=8) -> Nested Loop (cost=0.43..396254.63 rows=43823 width=8) Join Filter: (data.sensor_id = sensors.id) -> Index Scan using timestamp_ind on data (cost=0.43..254918.66 rows=4710976 width=12) -> Materialize (cost=0.00..6.70 rows=2 width=4) -> Seq Scan on sensors (cost=0.00..6.69 rows=2 width=4) Filter: (station_id = 4) (7 rows) QUERY PLAN (MAX) ---------------------------------------------------------------------------------------------------------- Aggregate (cost=3680.59..3680.60 rows=1 width=8) -> Nested Loop (cost=0.43..3571.03 rows=43823 width=8) -> Seq Scan on sensors (cost=0.00..6.69 rows=2 width=4) Filter: (station_id = 4) -> Index Only Scan using sensor_ind_timestamp on data (cost=0.43..1389.59 rows=39258 width=12) Index Cond: (sensor_id = sensors.id) (6 rows)
所以我的两个问题是:
>这种性能差异来自哪里?我已经在MIN/MAX vs ORDER BY and LIMIT看到了接受的答案,但这似乎并不适用于此.任何好的资源将不胜感激.
>有没有比添加EXISTS检查更好的方法来提高所有情况下的性能(匹配行与没有匹配的行)?
编辑以解决以下评论中的问题.我保留了上面的初始查询计划以供将来参考:
表定义:
Table "public.sensors" Column | Type | Modifiers ----------------------+------------------------+----------------------------------------------------------------- id | integer | not null default nextval('sensors_id_seq'::regclass) station_id | integer | not null .... Indexes: "sensor_primary" PRIMARY KEY,btree (id) "ind_station_id" btree (station_id,id) "ind_station" btree (station_id) Table "public.data" Column | Type | Modifiers -----------+--------------------------+------------------------------------------------------------------ id | integer | not null default nextval('data_id_seq'::regclass) timestamp | timestamp with time zone | not null sensor_id | integer | not null avg | integer | Indexes: "timestamp_ind" btree ("timestamp" DESC) "sensor_ind" btree (sensor_id) "sensor_ind_timestamp" btree (sensor_id,"timestamp") "sensor_ind_timestamp_desc" btree (sensor_id,"timestamp" DESC)
请注意,在下面的@Erwin建议之后,我刚刚在传感器上添加了ind_station_id.时间没有真正改变,在ORDER BY DESC LIMIT 1情况下仍然> 1200ms,在MAX情况下仍然是~0.9ms.
查询计划:
QUERY PLAN (ORDER BY) ---------------------------------------------------------------------------------------------------------- Limit (cost=0.58..9.62 rows=1 width=8) (actual time=2161.054..2161.054 rows=0 loops=1) Buffers: shared hit=3418066 read=47326 -> Nested Loop (cost=0.58..396382.45 rows=43823 width=8) (actual time=2161.053..2161.053 rows=0 loops=1) Join Filter: (data.sensor_id = sensors.id) Buffers: shared hit=3418066 read=47326 -> Index Scan using timestamp_ind on data (cost=0.43..255048.99 rows=4710976 width=12) (actual time=0.047..1410.715 rows=4710976 loops=1) Buffers: shared hit=3418065 read=47326 -> Materialize (cost=0.14..4.19 rows=2 width=4) (actual time=0.000..0.000 rows=0 loops=4710976) Buffers: shared hit=1 -> Index Only Scan using ind_station_id on sensors (cost=0.14..4.18 rows=2 width=4) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: (station_id = 4) Heap Fetches: 0 Buffers: shared hit=1 Planning time: 0.478 ms Execution time: 2161.090 ms (15 rows) QUERY (MAX) ---------------------------------------------------------------------------------------------------------- Aggregate (cost=3678.08..3678.09 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1) Buffers: shared hit=1 -> Nested Loop (cost=0.58..3568.52 rows=43823 width=8) (actual time=0.006..0.006 rows=0 loops=1) Buffers: shared hit=1 -> Index Only Scan using ind_station_id on sensors (cost=0.14..4.18 rows=2 width=4) (actual time=0.005..0.005 rows=0 loops=1) Index Cond: (station_id = 4) Heap Fetches: 0 Buffers: shared hit=1 -> Index Only Scan using sensor_ind_timestamp on data (cost=0.43..1389.59 rows=39258 width=12) (never executed) Index Cond: (sensor_id = sensors.id) Heap Fetches: 0 Planning time: 0.435 ms Execution time: 0.048 ms (13 rows)
就像前面解释的那样,ORDER BY使用timestamp_in对数据执行扫描,这在MAX情况下没有完成.
Postgres版本:
来自Ubuntu repos的Postgres:x86_64-unknown-linux-gnu上的Postgresql 9.4.5,由gcc编译(Ubuntu 5.2.1-21ubuntu2)5.2.1 20151003,64位
请注意,存在NOT NULL约束,因此ORDER BY不必对空行进行排序.
另请注意,我对差异的来源非常感兴趣.虽然不理想,但我可以使用EXISTS(<1ms)然后SELECT(~11ms)相对快速地检索数据.
解决方法
max()和ORDER BY DESC LIMIT之间存在实际差异1.很多人似乎都错过了. NULL值按降序排序顺序排序.因此,ORDER BY时间戳DESC LIMIT 1返回时间戳为IS NULL的行(如果存在),而聚合函数max()忽略NULL值并返回最新的非空时间戳.
对于您的情况,由于您的列d.timestamp被定义为NOT NULL(如您的更新所示),因此没有有效的区别.具有DESC NULLS LAST的索引和LIMIT查询的ORDER BY中的相同子句应该仍然是最好的.我建议这些索引(我的查询建立在第二个上):
sensor(station_id,id) data(sensor_id,timestamp DESC NULLS LAST@H_403_46@)
您可以删除其他索引变量sensor_ind_timestamp和sensor_ind_timestamp_desc,除非您有其他仍需要它们的查询(不太可能,但可能).
更重要的是,还有另一个困难:第一个表传感器上的过滤器返回很少,但仍然(可能)多行. Postgres希望在你添加的EXPLAIN输出中找到2行(rows = 2).
完美的技术将是第二个表数据的松散索引扫描 – 目前在Postgres 9.4(或Postgres 9.5)中没有实现.您可以通过各种方式重写查询以解决此限制.细节:
> Optimize GROUP BY query to retrieve latest record per user
最好的应该是:
SELECT d.timestamp FROM sensors s CROSS JOIN LATERAL ( SELECT timestamp FROM data WHERE sensor_id = s.id ORDER BY timestamp DESC NULLS LAST LIMIT 1 ) d WHERE s.station_id = 4 ORDER BY d.timestamp DESC NULLS LAST LIMIT 1;
由于外部查询的样式大多不相关,您还可以:
SELECT max(d.timestamp) AS timestamp FROM sensors s CROSS JOIN LATERAL ( SELECT timestamp FROM data WHERE sensor_id = s.id ORDER BY timestamp DESC NULLS LAST LIMIT 1 ) d WHERE s.station_id = 4;
并且max()变体现在应该执行速度快:
SELECT max(d.timestamp) AS timestamp FROM sensors s CROSS JOIN LATERAL ( SELECT max(timestamp) AS timestamp FROM data WHERE sensor_id = s.id ) d WHERE s.station_id = 4;
甚至,最短的:
SELECT max((SELECT max(timestamp) FROM data WHERE sensor_id = s.id)) AS timestamp FROM sensors s WHERE station_id = 4;
请注意双括号!
LIMATER在LATERAL连接中的另一个优点是,您可以检索所选行的任意列,而不仅仅是最新的时间戳(一列).
有关:
> Why do NULL values come first when ordering DESC in a PostgreSQL query?
> What is the difference between LATERAL and a subquery in PostgreSQL?
> Select first row in each GROUP BY group?
> Optimize groupwise maximum query