it's extended ticket CORE-1482

create table t1 (
  id int not null
);

create table t2 (
  id int not null,
  t1_id int not null
);

set term ^;

execute block
as
declare i int;
begin
  i = 1;
  while (i <= 100) do
  begin
    insert into t1(id) values(:i);
    i = i + 1;
  end
  i = 1;
  while (i < 1000000) do
  begin
    insert into t2(id, t1_id) values(:i, :i / 100000 + 1);
    i = i + 1;
  end
end^

set term ;^

commit;

alter table t1
add constraint pk_t1 primary key(id);

alter table t2
add constraint pk_t2 primary key(id);

alter table t2
add constraint fk_t2_t1 foreign key(t1_id) references t1(id);

select min(t1.id)
from t1
join t2 on t1.id = t2.t1_id;


Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Table "T2" Full Scan
            -> Filter
                -> Table "T1" Access By ID
                    -> Bitmap
                        -> Index "PK_T1" Unique Scan

         MIN
============
           1

Current memory = 11621440
Delta memory = 547712
Max memory = 11672536
Elapsed time= 3.302 sec
Buffers = 2048
Reads = 11421
Writes = 0
Fetches = 6026958 <-- no good

vs

select min(t2.id)
from t1
join t2 on t1.id = t2.t1_id;


Select Expression
    -> Aggregate
        -> Nested Loop Join (inner)
            -> Table "T2" Access By ID
                -> Index "PK_T2" Full Scan
            -> Filter
                -> Table "T1" Access By ID
                    -> Bitmap
                        -> Index "PK_T1" Unique Scan

         MIN
============
           1

Current memory = 11840168
Delta memory = 1672
Max memory = 11872248
Elapsed time= 0.058 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 38 <-- good

vs

same as 1 query, but much faster

select t1.id
from t1
join t2 on t1.id = t2.t1_id
order by t1.id rows 1;


Select Expression
    -> First N Records
        -> Nested Loop Join (inner)
            -> Table "T1" Access By ID
                -> Index "PK_T1" Full Scan
            -> Filter
                -> Table "T2" Access By ID
                    -> Bitmap
                        -> Index "FK_T2_T1" Range Scan (full match)

          ID
============
           1

Current memory = 11933608
Delta memory = 52288
Max memory = 11933760
Elapsed time= 0.062 sec
Buffers = 2048
Reads = 0
Writes = 0
Fetches = 137 <-- good