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

```

一個細節問題，平時留意即可。

PostgreSQL DBA(195) - Limit&執行計劃

相關文章