Hi Stephen!

You're telling us quite a bit, without really giving us important
information. The point with plans aren't to use indexes for every table
in the plan, but to use SELECTIVE indexes. How selective are each index?
All I can guess (so far), is that LDS_LOAD_KEY is not selective at all
in your query - it has to be used only against 'B.LDS_TEMPLATE_FLAG =
'N' ', so I guess this is an index on a Boolean field, and that is
normally far worse than using NATURAL (well, unless 95% of the records
have 'Y' in the field, and only a small fraction has 'N'). The two
primary keys should be brilliant, that leaves LD_LOAD_NUMBER_KEY, which
I assume to have poor selectivity since the query is time consuming.

Tell us a lot more about your indexes and their selectivity, the plan
for the decent performing query - and I also like to have a general idea
about the size of each table involved in the query.

Set

Stephen Boyd wrote:

> The following query appears to put Firebird 1.5.3 into a loop.
> Processor goes to 100% and the query never appears to finish.
>
> SELECT A.LD_LOCATION, A.LD_LOAD_NUMBER, A.LD_LOAD_SUFFIX, A.LD_LH_MILES,
> E.LD_LOCATION AS LOAD_LOC, E.LD_LOAD_NUMBER AS LOAD_NUM,
> E.LD_LOAD_SUFFIX AS LOAD_SFX, E.LD_NUM_STOPS, E.LD_TTL_REVENUE,
> E.LD_TTL_ASS_REVENUE, E.LD_TTL_CART_AMOUNT
> FROM LOADS A
> INNER JOIN LD_STOP B ON B.LDS_TEMPLATE_FLAG = 'N' AND
> B.LDS_LOCATION = A.LD_LOCATION AND
> B.LDS_LOAD_NUMBER = A.LD_LOAD_NUMBER AND
> B.LDS_LOAD_SFX = A.LD_LOAD_SUFFIX
> INNER JOIN MF_STOP_STOPS C ON C.MFSS_STOP_ID = B.LDS_STOP_ID
> INNER JOIN LD_STOP D ON D.LDS_STOP_ID = C.MFSS_LDS_STOP_ID
> INNER JOIN LOADS E ON E.LD_TEMPLATE_FLAG = 'N' AND
> E.LD_LOCATION = D.LDS_LOCATION AND
> E.LD_LOAD_NUMBER = D.LDS_LOAD_NUMBER AND
> E.LD_LOAD_SUFFIX = D.LDS_LOAD_SFX
> WHERE A.LD_TEMPLATE_FLAG = 'N' AND
> A.LD_MANIFEST_FLAG <> ' '
> ORDER BY A.LD_TEMPLATE_FLAG, A.LD_LOCATION, A.LD_LOAD_NUMBER,
> A.LD_LOAD_SUFFIX
>
> The plan shows that indices are being used for all JOINS:
>
> PLAN SORT (JOIN (B INDEX (LDS_LOAD_KEY),A INDEX (LD_LOAD_NUMBER_KEY),E
> INDEX (LD_LOAD_NUMBER_KEY),C INDEX (RDB$PRIMARY87),D INDEX
> (RDB$PRIMARY39)))
>
> The same query with the last JOIN (LOADS E) omitted completes in less
> than a minute. I have let the full query run for 15 minutes, with
> Firebird at 100% CPU the whole time, before I finally killed it.
>
> This is the query that works without problems. As I said before it
> runs in less that 1 minute with Firebird at 5% - 10% of CPU.
>
> SELECT A.LD_LOCATION, A.LD_LOAD_NUMBER, A.LD_LOAD_SUFFIX, A.LD_LH_MILES,
> D.LDS_LOCATION AS LOAD_LOC, D.LDS_LOAD_NUMBER AS LOAD_NUM,
> D.LDS_LOAD_SFX AS LOAD_SFX
> FROM LOADS A
> INNER JOIN LD_STOP B ON B.LDS_TEMPLATE_FLAG = 'N' AND
> B.LDS_LOCATION = A.LD_LOCATION AND
> B.LDS_LOAD_NUMBER = A.LD_LOAD_NUMBER AND
> B.LDS_LOAD_SFX = A.LD_LOAD_SUFFIX
> INNER JOIN MF_STOP_STOPS C ON C.MFSS_STOP_ID = B.LDS_STOP_ID
> INNER JOIN LD_STOP D ON D.LDS_STOP_ID = C.MFSS_LDS_STOP_ID
> WHERE A.LD_TEMPLATE_FLAG = 'N' AND
> A.LD_MANIFEST_FLAG <> ' '
> ORDER BY A.LD_TEMPLATE_FLAG, A.LD_LOCATION, A.LD_LOAD_NUMBER,
> A.LD_LOAD_SUFFIX
>
> Is this a bug or is there some way to restructure the query to make it
> run faster.