> 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.
I believe you are incorrectly using CTE instead of simple calculated column, as in: select i.*, ( select FIRST 1 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 igc.issue_id = i.issue_id and ia."USER" = 'a' order by ia.occasion desc ) as occasion from issues i Sean