准备数据

1
2
3
4
5
6
7
postgres=# create table tmax (id int);
CREATE TABLE
postgres=# insert into tm
tmax  tmp1
postgres=# insert into tmax values (generate_series(1,100000000));
INSERT 0 100000000
postgres=#

一亿条无索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
postgres=# explain analyze verbose select max(id) from tmax ;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1692478.40..1692478.41 rows=1 width=4) (actual time=32215.384..32215.385 rows=1 loops=1)
   Output: max(id)
   ->  Seq Scan on public.tmax  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=4.656..18766.501 rows=100000000 loops=1)
         Output: id
 Planning time: 0.132 ms
 Execution time: 32215.441 ms
(6 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1942478.48..1942478.48 rows=1 width=4) (actual time=32789.641..32789.641 rows=1 loops=1)
   Output: id
   ->  Sort  (cost=1942478.48..2192478.56 rows=100000032 width=4) (actual time=32789.640..32789.640 rows=1 loops=1)
         Output: id
         Sort Key: tmax.id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on public.tmax  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=15.948..18095.096 rows=100000000 loops=1)
               Output: id
 Planning time: 0.101 ms
 Execution time: 32789.678 ms
(10 rows)
postgres=# explain analyze verbose select max(id) from tmax ;
                                                              QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1692478.40..1692478.41 rows=1 width=4) (actual time=32411.383..32411.383 rows=1 loops=1)
   Output: max(id)
   ->  Seq Scan on public.tmax  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=10.210..18996.920 rows=100000000 loops=1)
         Output: id
 Planning time: 0.125 ms
 Execution time: 32411.436 ms
(6 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1942478.48..1942478.48 rows=1 width=4) (actual time=33312.476..33312.477 rows=1 loops=1)
   Output: id
   ->  Sort  (cost=1942478.48..2192478.56 rows=100000032 width=4) (actual time=33312.474..33312.474 rows=1 loops=1)
         Output: id
         Sort Key: tmax.id
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Seq Scan on public.tmax  (cost=0.00..1442478.32 rows=100000032 width=4) (actual time=8.584..18551.514 rows=100000000 loops=1)
               Output: id
 Planning time: 0.100 ms
 Execution time: 33312.514 ms
(10 rows)
postgres=#

看执行结果是max快

一亿条，有索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
postgres=# create index id_tmax_index on tmax (id);
CREATE INDEX
postgres=#
postgres=# explain analyze verbose select max(id) from tmax ;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.60..0.61 rows=1 width=0) (actual time=0.041..0.042 rows=1 loops=1)
   Output: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.035..0.035 rows=1 loops=1)
           Output: tmax.id
           ->  Index Only Scan Backward using id_tmax_index on public.tmax  (cost=0.57..3289257.57 rows=100000000 width=4) (actual time=0.033..0.033 rows=1 loops=1)
                 Output: tmax.id
                 Index Cond: (tmax.id IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.198 ms
 Execution time: 0.092 ms
(11 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.029..0.030 rows=1 loops=1)
   Output: id
   ->  Index Only Scan Backward using id_tmax_index on public.tmax  (cost=0.57..3039257.57 rows=100000000 width=4) (actual time=0.027..0.027 rows=1 loops=1)
         Output: id
         Heap Fetches: 1
 Planning time: 0.143 ms
 Execution time: 0.065 ms
(7 rows)
postgres=# explain analyze verbose select max(id) from tmax ;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.60..0.61 rows=1 width=0) (actual time=0.042..0.043 rows=1 loops=1)
   Output: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.036..0.037 rows=1 loops=1)
           Output: tmax.id
           ->  Index Only Scan Backward using id_tmax_index on public.tmax  (cost=0.57..3289257.57 rows=100000000 width=4) (actual time=0.034..0.034 rows=1 loops=1)
                 Output: tmax.id
                 Index Cond: (tmax.id IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.199 ms
 Execution time: 0.092 ms
(11 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                         QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.030..0.030 rows=1 loops=1)
   Output: id
   ->  Index Only Scan Backward using id_tmax_index on public.tmax  (cost=0.57..3039257.57 rows=100000000 width=4) (actual time=0.029..0.029 rows=1 loops=1)
         Output: id
         Heap Fetches: 1
 Planning time: 0.144 ms
 Execution time: 0.073 ms
(7 rows)
postgres=#

有索引，就是order by 快一点点.

有索引，并且是倒序索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
postgres=# create index id_tmax_index on tmax (id desc);
CREATE INDEX
postgres=#
postgres=# explain analyze verbose select max(id) from tmax ;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.60..0.61 rows=1 width=0) (actual time=0.076..0.076 rows=1 loops=1)
   Output: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.071..0.071 rows=1 loops=1)
           Output: tmax.id
           ->  Index Only Scan using id_tmax_index on public.tmax  (cost=0.57..3289257.57 rows=100000000 width=4) (actual time=0.069..0.069 rows=1 loops=1)
                 Output: tmax.id
                 Index Cond: (tmax.id IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.200 ms
 Execution time: 0.128 ms
(11 rows)
postgres=# explain analyze verbose select max(id) from tmax ;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.60..0.61 rows=1 width=0) (actual time=0.075..0.076 rows=1 loops=1)
   Output: $0
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.068..0.069 rows=1 loops=1)
           Output: tmax.id
           ->  Index Only Scan using id_tmax_index on public.tmax  (cost=0.57..3289257.57 rows=100000000 width=4) (actual time=0.067..0.067 rows=1 loops=1)
                 Output: tmax.id
                 Index Cond: (tmax.id IS NOT NULL)
                 Heap Fetches: 1
 Planning time: 0.184 ms
 Execution time: 0.123 ms
(11 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.055..0.055 rows=1 loops=1)
   Output: id
   ->  Index Only Scan using id_tmax_index on public.tmax  (cost=0.57..3039257.57 rows=100000000 width=4) (actual time=0.053..0.053 rows=1 loops=1)
         Output: id
         Heap Fetches: 1
 Planning time: 0.133 ms
 Execution time: 0.098 ms
(7 rows)
postgres=# explain analyze verbose select id from tmax order by id desc limit 1 ;
                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.60 rows=1 width=4) (actual time=0.054..0.054 rows=1 loops=1)
   Output: id
   ->  Index Only Scan using id_tmax_index on public.tmax  (cost=0.57..3039257.57 rows=100000000 width=4) (actual time=0.052..0.052 rows=1 loops=1)
         Output: id
         Heap Fetches: 1
 Planning time: 0.135 ms
 Execution time: 0.098 ms
(7 rows)
postgres=#

有索引，并且是倒序索引，也还是order by 快一点点

推测

max在无索引，还是有索引，综合好一点，所以统一习惯写max().