OK, then I have no idea why it chose the plan it did (other than
possibly duplicate indexes). Tell us which version of Firebird (or
InterBase) you use and then hopefully Arno, Ann or someone else can
give an explanation. I for one would be interested in knowing why your
simple query

SELECT *
FROM TMP_DRUGQUANTITY dq
JOIN TMP_GPI g ON g.NDC = dq.NDC
LEFT JOIN TMP_LABELNAME ln ON ln.NDC = dq.NDC
WHERE dq.ndc=173069502 or dq.ndc=173069500

resulted in this plan

PLAN JOIN (JOIN (G NATURAL,DQ INDEX
(PK_DRUGQUANTITY,PK_DRUGQUANTITY,PK_DRUGQUANTITY)),
LN INDEX(PK_TMP_LABELNAME))

rather than

PLAN JOIN (JOIN (DQ INDEX (PK_DRUGQUANTITY,PK_DRUGQUANTITY),
G INDEX (PK_TMP_GPI)),LN INDEX (PK_TMP_LABELNAME))

Set

--- In firebird-support@yahoogroups.com, "Rick Debay" wrote:
> Normally the two tables have the same number of rows. In this case,
> as I didn't keep my development system up to date, g had more rows.
> Since these are temporary tables until I integrate the Medispan
> database properly into our system, there are no foreign keys, just
> the primary key on NDC in each table.
> Using g.NDC = dq.NDC+0, the plan is
> PLAN JOIN (JOIN (DQ INDEX (PK_DRUGQUANTITY,PK_DRUGQUANTITY),
> G INDEX (PK_TMP_GPI)),LN INDEX (PK_TMP_LABELNAME))
> If it had to use NATURAL for some reason, dq has smaller records so
> more would fit in a page, making it a better choice to step through
> (more of the table would remain in cache). I doubt that is one of
> the rules the optimzer uses, though.
>
> This query is the basis for a view. I need the view in order to
> create an interface to the Medispan database, so I can recreate it
> properly without impacting the rest of the system. However, a query
> that takes over 200 times longer than it should has an impact of a
> different sort.