本文介绍了max()VS ORDER BY DESC+LIMIT 1的性能的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我今天排除了几个速度较慢的SQL查询的故障,不太了解下面的性能差异:

根据某些条件尝试从数据表中提取max(timestamp)时,如果存在匹配行,使用MAX()ORDER BY timestamp 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)

所以我的两个问题是:

  1. 这种性能差异从何而来?我在这里看到了公认的答案MIN/MAX vs ORDER BY and LIMIT,但这似乎不太适用于这里。任何好的资源都将不胜感激。
  2. 是否有比添加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_idon[2-7]>。计时实际上并没有发生重大变化,仍然是ORDER BY DESC + LIMIT 1案例中的>1200msMAX案例中的~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执行Scan using timestamp_in on data,而在MAX情况下没有执行。

Postgres版本:来自Ubuntu Repos的帖子:PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 5.2.1-21ubuntu2) 5.2.1 20151003, 64-bit

请注意,有NOT NULL个约束,因此ORDER BY不必对空行进行排序。

还请注意,我很感兴趣的是差异来自哪里。虽然不理想,但我可以使用EXISTS (<1ms),然后使用SELECT (~11ms)相对较快地检索数据。

推荐答案

sensor.station_id上似乎没有索引,这在这里很可能很重要。

max()ORDER BY DESC + LIMIT 1之间存在实际差异。很多人似乎都错过了这一点。空值按降序排列第一个。因此ORDER BY timestamp DESC LIMIT 1返回带有timestamp IS NULL的行(如果存在),而聚合函数max()忽略空值并返回最新的非空时间戳。

对于您的情况,由于您的列d.timestamp定义为NOT NULL(如您的更新所示),因此没有有效的区别。具有DESC NULLS LASTORDER BY查询的ORDER BY中相同子句的索引应该仍然最适合您。我建议使用这些索引(我下面的查询建立在第二个索引的基础上):

sensor(station_id, id)
data(sensor_id, timestamp DESC NULLS LAST)

您可以删除其他索引变量和,除非您有仍然需要它们的其他查询(不太可能,但可能)。

更重要的,还有另一个困难:第一个表sensors上的过滤返回的行很少,但仍然(可能)返回多行。Postgres希望在添加的EXPLAIN输出中找到2行(rows=2)。
完美的技术是对第二个表data松散索引扫描-这在Postgres 9.4(或Postgres 9.5)中当前没有实现。您可以通过多种方式重写查询以绕过此限制。详细信息:

最好是:

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;

注意双括号!

LIMITLATERAL联接中的另一个优点是,您可以检索所选行的任意列,而不仅仅是最新的时间戳(一列)。

相关:

这篇关于max()VS ORDER BY DESC+LIMIT 1的性能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-19 04:18