Thanks a lot SET for the help and the explanation. I will keep a look out for the plans and look for such NATURALs

Kind regards
Bhavbhuti

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >Amazing!!! Look at the new plan (speed) below!
> >
> >Due to this the amalgamated report also works in a second or two, thanks a million S.E.T.
>
> Congratulations, reducing the execution time from 20-25 minutes to a few seconds sounds very good.
>
> >So is it good advise to look for the NATURAL in plan and set indexes of the related
> >table.field? Or there is more thoughts to it than just this? I still do see a NATURAL in
> >the plan below but the fields IID (which is a PK) and IPID (which is a FK) have index on
> >them, why would that be? Though many FKs in the database have index but there are still
> >some that do not have a constraint on them thus do not have an index, is it a good practice
> >to have all FK indexed constraint or not?
>
> There's a bit more thought required than just setting indexes every NATURAL, although looking for NATURAL in your plan is a good starting point for further analysis and experiments. Normally (unless the table is big), it doesn't matter if the first table in your plan is NATURAL, but any later occurrence may mean (in my simplified understanding, it is not how Firebird really implements things, but I've never understood that) that NATURAL is used not once for the whole table, but iteratively for each potential record. Hence, I think I'd prefer NATURAL for the first table in the plan if that table contains 1 million records over NATURAL for a table containing 50000 records that appear later in the plan.
>
> In your case, no index can be used for the remaining NATURAL. In order for an index to be used, you would need to remove 'OR NOT EXISTS (SELECT 1 FROM mItems d WHERE d.iPID = p.iID)' and then have an index for p.lHierarchialStock. That means that the result would be different, and since I guess 1 isn't very selective for lHierarchialStock, the result could be that it took longer to use an index than to go NATURAL. So I think your query now is about as good as it gets.
>
> Set
>