recreate table tmp(f1 int, f2 int); commit;
set stat on;
set term ^;
execute block as
declare variable n int = 3000000;
begin
while (n>=0) do 
  insert into tmp values(rand()*1000,rand()*1000)
  returning :n-1 into :n;
end^
set term ;^
commit;
set stat off;
create index tmp_f1 on tmp(f1);
set stat on;
commit;
set stat off;

          F1               CNT_MAX
============ =====================
          100                     3
         200                     3
         111                     2 

set plan on; set stat on;
select a.f1, a.cnt_max from (select a.f1, count(*) cnt_max from tmp a group by a.f1) a 
join (select distinct cnt 
        from (select count(*) cnt from tmp x group by x.f1) 
       order by cnt desc rows 2) x
on a.cnt_max = x.cnt;

PLAN HASH (SORT (X X ORDER TMP_F1), A A ORDER TMP_F1)

Current memory = 590120360
Delta memory = 4042040
Max memory = 641591496
Elapsed time= 17.49 sec   17.12   17.14   17.13   17.14
Buffers = 131072
Reads = 0
Writes 0
Fetches = 18007506

PLAN MERGE (SORT (SORT (X X ORDER TMP_F1)), SORT (A A ORDER TMP_F1))

Current memory = 560854636
Delta memory = 4
Max memory = 612267964
Elapsed time= 14.45 sec   14.45   14.45   14.45   14.45 // да-да! пять раз получалось одинаковое время!
Buffers = 131072
Reads = 0
Writes 0
Fetches = 18007418

для 3.0.0:   17.27   17.25   17.21   17.24   17.24
для 2.5.3:   14.52   14.53   14.52   14.53   14.52

select cnt 
from (select count(*) cnt from tmp x group by x.f1) 
order by cnt
rows 2;

set plan on; set stat on;
select a.f1, a.cnt_max from (select a.f1, count(*) cnt_max from tmp a group by a.f1) a 
join (select distinct cnt 
        from (select count(*) cnt from tmp x group by x.f1) 
       order by cnt desc rows 2) x
on a.cnt_max = x.cnt;
set stat off;
commit;

SQL> recreate table t1(x int); commit;
SQL> recreate table t2(x int); commit;
SQL> insert into t1 select rand()*100 from rdb$types,rdb$types rows 1000;
SQL> insert into t2 select rand()*100 from rdb$types,rdb$types rows 10000;
SQL> commit;
SQL> out nul; set stat on; set plan on;
-- искуственно запрещаем ему делать hash: отрубаем условие равенства, 
-- заменяя его between'ом с границами, аналогичными строгому равенству:
SQL> select a.x,count(*) from t1 a join t2 b on a.x  between b.x and b.x  group by a.x;

recreate table tmp(f1 int, f2 int); commit;
set term ^;
execute block as
declare variable n int = 10000000;
begin
while (n>=0) do 
  insert into tmp values(rand()*1000,rand()*1000)
  returning :n-1 into :n;
end^
set term ;^
commit;
----------------------
out nul;
set plan on;
set stat on;
select a.f1, a.cnt_max from (select a.f1, count(*) cnt_max from tmp a group by a.f1) a 
join (select distinct cnt 
        from (select count(*) cnt from tmp x group by x.f1) 
       order by cnt desc rows 2) x
on a.cnt_max = x.cnt;

PLAN MERGE (SORT (SORT (SORT (X X NATURAL))), SORT (SORT (A A NATURAL)))
3 records fetched
  402928  ms, 266808 read(s), 40533616 fetch(es)

Table                             Natural     Index    Update    Insert
xpunge
**************************************************************************
******
TMP                              20000002

PLAN HASH (SORT (SORT (X X NATURAL)), SORT (A A NATURAL))
2 records fetched
  481901  ms, 266817 read(s), 40266815 fetch(es)

Table                             Natural     Index    Update
xpunge
**************************************************************
******
TMP                              20000002