Alec,

> Sean,
>
> The query you proposed will not produce the same result because you are
> sorting and topping the join before applying the filter.

You are correct.

But a small change to my proposed query would resolve that issue, the new/corrected query would be:

SELECT
FIRST (1000)
PHYSICAL_COPY."ID", ...
FROM (
SELECT
PHYSICAL_COPY."ID"
FROM PHYSICAL_COPY
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID" ) AbstractTable
WHERE
(PHYSICAL_COPY."IS_DIRTY" = 1) AND
(COPY."SOURCE_ID" = '123431234') AND
(PHYSICAL_COPY."COMMIT_NUMBER" >= 10000000)
ORDER BY
PHYSICAL_COPY."COMMIT_NUMBER"
) AbstractTable
INNER JOIN PHYSICAL_COPY on PHYSICAL_COPY."ID" = AbstractTable."ID"
INNER JOIN COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"

> We have a one-to-one relationship between PHYSICAL_COPY and COPY and
> COPY_CLASSIFICATION. So, the solution I went with was to replace INNER
> JOINs with LEFT JOINs which changed the execution plan from (I translated
> index names):


> This is the second time (see my older post "Force query plan to filter before
> join") where LEFT join saved the day. It would be nice to understand why
> LEFT join optimization works so much better for us. Is there an explanation of
> FB optimization techniques available somewhere?

What is the performance of your query like if you change the query to be:

SELECT
PHYSICAL_COPY."ID", ...
FROM COPY
INNER JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
INNER JOIN COPY_CLASSIFICATION ON COPY."ID" = COPY_CLASSIFICATION."COPY_ID"
WHERE ...

(the position of COPY and PHYSICAL_COPY in the query was changed, but the defined relationship is the same)


Sean