Can not achieve good performance in query with filtered columns outside of inner part with RANK() function. These columns participate in "partition by" expression of RANK(), bet filter isn't pushed down. Same functionality in Oracle works fine.

Short example.

Good performance:

select ml_id, nr, da
,rank() over (partition by ml_id, nr, da order by nr) r
from ml, iger, days
where ml_id = 4 and nr = 1 and da = date(now())

Bad performance (desired query):

select * from (
select ml_id, nr, da
,rank() over (partition by ml_id, nr, da order by nr) r
from ml, iger, days
) a where ml_id = 4 and nr = 1 and da = date(now())

Plan:

1 PRIMARY <derived2> ALL \N \N \N \N 22991097024000 Using where
2 DERIVED ml index \N ml_aggf_i 1 \N 12150 Using index; Using temporary
2 DERIVED days index \N PRIMARY 3 \N 21760 Using index; Using join buffer (flat, BNL join)
2 DERIVED iger index \N PRIMARY 3 \N 86961 Using index; Using join buffer (incremental, BNL join)

What could be done to improve?