Hi,

> I've been trying to optimize a query that joins two tables and has one
> additional condition on each of them:

> insert into TARGET (...)
> select ...
> from A
> inner join F
> on A.FK = F.PK
> where F.Status in ('1', '2')
> and A.Status in ('1', '2')

> Executing this query took about 13 hours without an index I should have
> had on one of the two left-joined tables. Adding that index probably
> brought the execution time down a bit, but according to my tests with
> "first 1000" it would still have taken several hours to execute (more
> than 10).
> After a lot of experimenting I found that if I drop the F.Status
> condition (which I realized I could do due to "business logic"), the
> query executes a *lot* faster! I also bumped up memory usage on the
> server, and I was now down to 30 minutes.

Assuming the PLAN with your query looks like :

PLAN JOIN(A INDEX (IDX_A_STATUS, IDX_A_STATUS),
F INDEX (IDX_F_FK_A, IDX_F_STATUS, IDX_F_STATUS))

The F.Status will probably have a low selectivity (many duplicated values). Due
this first all index-pages with the key containg '1' and '2' are read. Those are
combined together with the index that belongs to the FK, but the FK (i guess) is
more selectivity and have to read only a few or 1 index-page.
For every record read from A those index-pages for status '1' and '2' and the
FK-index are read, which is an expensive task.

> Can anyone explain why the F.Status condition ruins performance and how
> I should reformulate the query and/or what indices I should add to make
> it execute faster, in case I actually do need the F.Status condition?

When removing the F.Status index only the FK-index is read and probably much
cheaper, because that's a more selective index.

> I did try a lot of combinations of single-column as well as compound
> indices om both A and F. Didn't try *all* possible combos though.

Did you try to create a compound index on table F (FK, Status) and changed the
query to :

SELECT
...
FROM
A
JOIN F ON ((A.FK = F.PK and F.Status = '1') or
(A.FK = F.PK and F.Status = '2'))
where
A.Status IN ('1', '2')

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info