Alex,

> I am sorry I was not clear on my objectives. The first goal is to
> understand why commenting out either INNER join causes the query
> to run 20 times faster even though the first query in the join
> always returns a fixed number of rows (in this case 0 rows).

First, your commented out queries are not equivalent.

> The second goal is to optimize the query to run faster.
>
> I am using Firebird 2.1.2. Here are some query plans:
>
> -- Your query
> SELECT * FROM PHYSICAL_COPY a
> INNER JOIN COPY_CLASSIFICATION ON a."COPY_ID" =
> COPY_CLASSIFICATION."COPY_ID"
> INNER JOIN COPY ON COPY_CLASSIFICATION.COPY_ID = COPY."ID"
> WHERE (a."REPOSITORY_TYPE" = 'blah')
>
> PLAN JOIN (COPY_CLASSIFICATION NATURAL, A INDEX
> (IDX_/F13/OidwbfyhwFbjRjfng==), COPY INDEX
> (PK_ZM6SRonqR8AHSQuCISgvnQ==))
> 334757 fetches, 0 marks, 4209 reads, 0 writes.
> 0 inserts, 0 updates, 0 deletes, 41609 index, 41586 seq.
> Delta memory: -1000 bytes.
> Total execution time: 0.829s

Your problem is because you don't have an index on COPY_CLASSIFICATION."COPY_ID"

Create one and things will be much better.


Sean