>Hello,
>
>I need help optimizing the query shown below. PROJECT-related tables
>contain 12 rows each while COPY-related tables contain 14K rows each.
>Moreover, PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED'
>filters out all but 10 joined rows.
>
>My question is how do I change the plan to force
>PROJECT."ASSIGNMENT_STATUS" != 'UNASSIGNED' filter to be
>applied before the join?

Hi Alec!

I don't quite understand what you mean by 'filters out all but 10 joined rows', != (not equal) would normally not be able to use any index and trying to force that to be applied before any JOIN would of course find all that are different from 'UNASSIGNED' (note that NULL is neither equal to or different from 'UNASSIGNED') regardless of their connection to any other table, and making an index for a field where all but 10 rows have the same value would only be useful in very special circumstances. You can of course try something like

WITH MyTemp AS
(SELECT DISTINCT p.ID, p.PRIMARY_PROJECT_CODE
FROM PROJECT p
WHERE p.ASSIGNMENT_STATUS != 'UNASSIGNED')

SELECT tmp.PRIMARY_PROJECT_CODE AS COL0, COUNT(pc.ID) AS COL1
FROM PROJECT_CODE_DESCRIPTOR pcd
JOIN MyTemp tmp ON pcd.PROJECT_ID = tmp.ID
JOIN COPY_CLASSIFICATION cc ON pcd.PROJECT_CODE = cc.CLASSIFICATION_CODE
JOIN COPY c ON cc.COPY_ID = c.ID
JOIN PHYSICAL_COPY pc ON c.ID = pc.COPY_ID
WHERE pc.IS_MARKED_DELETED = 0
AND pc.IS_RECYCLED = 0
GROUP BY tmp.PRIMARY_PROJECT_CODE

but my guess is that this might be slower than your original query (and I'm not certain Firebird would choose the PLAN I'm hoping for, we might have to change things), Note that I'm not saying it is impossible to speed up your query, we just don't know enough about your tables and how selective your indexes are etc. so any suggestion will be more or less guesswork.

Set