GeXiangDong

精通Java、SQL、Spring的拼写，擅长Linux、Windows的开关机

0%

PostgreSQL: select max(x) vs select x ... order by x desc limit 1

发表于

在通常状况下（不利用索引），用max来查询一个字段的最大值是最佳选择，使用oder by这种方式相比max肯定是一个糟糕至极的选择。

但是在恰当的利用索引的情况下，PostgreSQL order by limit 1 这种方式给出了不同的结果。

比较 I（where中包含一个字段）

环境

table

1
2
3
4
create table user_login_record(
  user_id         char(36) not null,
  login_date    timestamp  not null
);

上面这个表（请忽略它并不符合实际的业务或者编码设计规范），有2个字段，我们根据user_id 来查询最后一个 login_date

我们忽略掉无索引的状况（正如前文所说max在无索引时性能最佳），直接建立索引

1
create index user_login_date on user_login_record (user_id, login_date desc);

比较结果

我们来比较2个SQL的性能

max

1
2
3
4
explain (analyze, buffers, costs) 
select max(login_date) 
from user_login_record 
where user_id='952bd155-06b3-4792-82ec-4b86d06c86a7'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.074..0.075 rows=1 loops=1)
   Buffers: shared hit=4
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.41..0.45 rows=1 width=8) (actual time=0.071..0.071 rows=1 loops=1)
           Buffers: shared hit=4
           ->  Index Only Scan using user_login_date on user_login_record  (cost=0.41..32.76 rows=1077 width=8) (actual time=0.070..0.070 rows=1 loops=1)
                 Index Cond: ((user_id = '952bd155-06b3-4792-82ec-4b86d06c86a7'::bpchar) AND (login_date IS NOT NULL))
                 Heap Fetches: 0
                 Buffers: shared hit=4
 Planning Time: 0.124 ms
 Execution Time: 0.097 ms
(11 rows)

order by … desc limit 1

1
2
3
4
5
6
explain (analyze, buffers, costs) 
select login_date 
from user_login_record 
where user_id='952bd155-06b3-4792-82ec-4b86d06c86a7'
order by login_date desc 
limit 1
1
2
3
4
5
6
7
8
9
10
11
                                                                    QUERY PLAN                                                                    
--------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..0.44 rows=1 width=8) (actual time=0.036..0.036 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using user_login_date on user_login_record  (cost=0.41..30.06 rows=1077 width=8) (actual time=0.034..0.035 rows=1 loops=1)
         Index Cond: (user_id = '952bd155-06b3-4792-82ec-4b86d06c86a7'::bpchar)
         Heap Fetches: 0
         Buffers: shared hit=4
 Planning Time: 0.067 ms
 Execution Time: 0.049 ms
(8 rows)

结论

两个都利用了索引的 login_date 字段，只取了最后一条参与计算，但是max多了一个循环，速度略差。

比较 II（where中包含三个字段）

两个字读的状况我也测试比较过，和一个字段情形相同，max 查询也利用了索引，只取了最后一条，也是多了一个循环。 单独拿出来3个字段，是因为3个字段的结果和1-2个字段不同。

环境

table

1
2
3
4
5
6
create table user_login_record(
  group_id       char(36) not null,
  user_id         char(36) not null,
  machine_id   char(32) not null
  login_date    timestamp  not null
);

上面这个表有4个字段，相比之前多了group_id, machine_id，可以理解为用户在某个组内用某台机器登陆的记录。我们根据 group_id, user_id和machine_id 来查询最后一个 login_date

依旧直接建立索引

1
create index user_login_date on user_login_record (group_id, user_id, machine_id, login_date desc);

比较结果

max

1
2
3
4
5
6
explain (analyze, buffers, costs) 
select max(login_date) 
from user_login_record 
where group_id='312bb069-fd27-4822-885d-c3ac67bfd8a1' 
  and user_id='952bd155-06b3-4792-82ec-4b86d06c86a7' 
  and machine_id='C635F8F44F1960778CE58869DF10150D';
1
2
3
4
5
6
7
8
9
10
11
                                                                                                   QUERY PLAN                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2.84..2.85 rows=1 width=8) (actual time=0.143..0.143 rows=1 loops=1)
   Buffers: shared hit=72
   ->  Index Only Scan using user_login_date on user_login_record  (cost=0.41..2.84 rows=1 width=8) (actual time=0.050..0.126 rows=98 loops=1)
         Index Cond: ((group_id = '312bb069-fd27-4822-885d-c3ac67bfd8a1'::bpchar) AND (user_id = '952bd155-06b3-4792-82ec-4b86d06c86a7'::bpchar) AND (machine_id = 'C635F8F44F1960778CE58869DF10150D'::bpchar))
         Heap Fetches: 98
         Buffers: shared hit=72
 Planning Time: 0.139 ms
 Execution Time: 0.163 ms
(8 rows)

order by … desc limit 1

1
2
3
4
5
6
7
8
explain (analyze, buffers, costs) 
select login_date
from user_login_record 
where group_id='312bb069-fd27-4822-885d-c3ac67bfd8a1' 
  and user_id='952bd155-06b3-4792-82ec-4b86d06c86a7' 
  and machine_id='C635F8F44F1960778CE58869DF10150D'
order by login_date desc
limit 1
1
2
3
4
5
6
7
8
9
10
11
                                                                                                 QUERY PLAN                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..2.84 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=1)
   Buffers: shared hit=4
   ->  Index Only Scan using user_login_date on user_login_record  (cost=0.41..2.84 rows=1 width=8) (actual time=0.041..0.041 rows=1 loops=1)
         Index Cond: ((group_id = '312bb069-fd27-4822-885d-c3ac67bfd8a1'::bpchar) AND (user_id = '952bd155-06b3-4792-82ec-4b86d06c86a7'::bpchar) AND (machine_id = 'C635F8F44F1960778CE58869DF10150D'::bpchar))
         Heap Fetches: 1
         Buffers: shared hit=4
 Planning Time: 0.124 ms
 Execution Time: 0.057 ms
(8 rows)

结论

两个都利用了索引，但利用方式有所不同，max没有利用login_date字段的排序，最后对符合条件的98条数据进行了筛选(rows=98 loops=1)；order by limit 1的sql则依旧利用了索引中的 login_date 字段，只取了一套数据(rows=1 loops=1)。
虽然这次max没多一次循环，但由于多了多条记录，相比另外一个sql性能降低了很多。

最后

随着PostgreSQL的升级（我的测试环境12和14版）以及不同环境的数据库会对sql采取不同优化措施。本文中的测试结果也可能会不同。每个人应该在自己的环境中做实际的测试来选择用那个sql以获得最佳的性能。