PostgreSQL MAX()和Order by DESC limit 1
Contents
准备数据
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=#
一亿条无索引
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快
一亿条，有索引
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 快一点点.
有索引，并且是倒序索引
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().