> All I can guess (so far), is that LDS_LOAD_KEY is not selective at all
> in your query

LDS_LOAD_KEY is a unique, compound key consisting of
LDS_TEMPLATE_FLAG, LDS_LOCATION, LDS_LOAD_NUMBER, LDS_LOAD_SFX AND
LDS_STOP_NUMBER. There will never be more that 20 rows with different
stop numbers for each load. So it is quite selective.

> that leaves LD_LOAD_NUMBER_KEY, which
> I assume to have poor selectivity since the query is time consuming.
>

LD_LOAD_NUMBER key is a unique, compound key consisting of
LD_TEMPLATE_FLAG, LD_LOAD_NUMBER and LD_SUFFIX. It is highly selective.

The only part of the query that should require a scan of a large
number of records in the stuff in the WHERE clause. That would have
to scan 40K records to find the 2K that I am interested in. None of
the JOINs should be reading more that 20 rows to get the rows that I
am interested in.

> and I also like to have a general idea
> about the size of each table involved in the query.
>

LOADS = 40K rows
LD_STOP = 100K rows
MF_STOP_STOPS = 20K rows

I find it curious that everything is fine until I JOIN the query back
to the LOADS table which, according to the plan, is using a unique key
to find the load records.