> We have a very simple query (shown below) that joins two tables,
> filters on a column in each table and projects the id from one of the
> tables. The query takes dozens of minutes to return when run on a 4.5
> GB database. We are looking for recommendations on optimizing the
> query. References to documentation relevant to query optimization will
> be appreciated as well.

First thing I would try, _especially_ since you use inner joins, would
be changing the order of the tables: Instead of
SELECT a.id, b.id FROM a JOIN b WHERE ...
try
SELECT a.id, b.id FROM b JOIN a WHERE ...

Second thing, it's been my experience that inner joins tend to be much
slower than outer joins with Firebird (probably a problem with my
optimizations, but the end result is that I am getting much better
performance from SELECT a.id, b.id FROM a LEFT JOIN b WHERE ... than
from SELECT a.id, b.id FROM a JOIN b WHERE ...

It seems to me your query could be changed to an outer-join query
without changing results:

SELECT FIRST 1000 PHYSICAL_COPY."ID" AS COL0
FROM COPY
JOIN PHYSICAL_COPY ON PHYSICAL_COPY."COPY_ID" = COPY."ID"
WHERE (PHYSICAL_COPY."IS_MARKED_DELETED" = 1) AND (COPY."SOURCE_ID" =
'fb50a346-04e7-42d3-8782-1432001c74ad')

Pepak