As soon as i add the ROWS clause to a CTE FB cannot use indices available in the CTEs tables.

Is this a bug, or as designed?

This:

with low as (

  select igc.issue_id, ia.occasion

  from issues_groups igc

    left join issues_groups igb on (igb.issue_significant_id = igc.issue_significant_id)

      left join issues_addressed ia on (ia.issue_id = igb.issue_id)

  where ia."USER" = 'a'

  --order by ia.occasion desc

  --fetch first 1 row only

)

select *

from issues i

  left join low l on (l.issue_id = i.issue_id)

issues_groups, issues_addressed: Indexed reads

remove the comments and issues_groups will have non-indexed reads for the join l.issue_id to i.issue_id.

TIA,

/Dany