PostgreSQL在使用Limit但没有显示指定order by的情况下，结果可能会不一样，原因是不同的执行计划会导致返回的结果不一样。

**测试脚本**
```
-- 脚本
drop table t;
create table t(id int ,c1 int,c2 int);

insert into t select x,mod(x,100),x from generate_series(1,10000) as x;
insert into t select x,mod(x,100),x from generate_series(1,10000) as x;

select c1,count(*) from t group by c1 limit 2;

-- 执行结果
testdb=# insert into t select x,mod(x,100),x from generate_series(1,10000) as x;
INSERT 0 10000
testdb=# insert into t select x,mod(x,100),x from generate_series(1,10000) as x;
INSERT 0 10000
testdb=#
testdb=# select c1,count(*) from t group by c1 limit 2;
 c1 | count
----+-------
 87 |   200
 71 |   200
(2 rows)

testdb=# explain analyze  select c1,count(*) from t group by c1 limit 2;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=442.54..442.56 rows=2 width=12) (actual time=10.778..10.780 rows=2 loops=1)
   ->  HashAggregate  (cost=442.54..444.54 rows=200 width=12) (actual time=10.776..10.777 rows=2 loops=1)
         Group Key: c1
         Batches: 1  Memory Usage: 48kB
         ->  Seq Scan on t  (cost=0.00..331.36 rows=22236 width=4) (actual time=0.010..2.804 rows=20000 loops=1)
 Planning Time: 0.067 ms
 Execution Time: 10.824 ms
(7 rows)

-- 禁用hashagg，用groupagg，结果不同
testdb=# set enable_hashagg = off;
SET
testdb=# select c1,count(*) from t group by c1 limit 2;
 c1 | count
----+-------
  0 |   200
  1 |   200
(2 rows)

testdb=#

-- 从结果上来也符合预期，group agg是先排序再聚集，因此前2行是0和1

```

一个细节问题，平时留意即可。

ITPub博客

PostgreSQL DBA(195) - Limit&执行计划

最新文章