Svein Erling Tysvжr wrote:

> >An odd thing happens when I've just added a sting field (varchar(255)) to
> >"PCategories":
> >that query runs faster about 10 times.
> >What happened? I can't explain that.
>
> Strange - and interesting. Hope someone can explain why. What happens if
> you try
>
>
> Is it still slow? And does the added field still effect execution time?

Results of your version are exactly same - slow without that field and fast
with it.
As I see the execution plan depends of that field presence. By the way
"Category_related" is a view.
It looks like:

select I.id, I.PCategory, ..., C.name as category
from Items I,..., PCategories C
where I.PCategory = C.id ... /* I.PCategory defined as FK references
PCategories (id) */

and contains about 15000 records.

The "slow" plan is:

PLAN (
PC INDEX (RDB$PRIMARY3,RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories ID,
ParentID->ID */
)
PLAN JOIN (
PC INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC2 INDEX (RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories.ParentID ->
PCategories.ID */
)
PLAN JOIN (
PC INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC2 INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC3 INDEX (RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories ParentID -> ID */
)
PLAN SORT (JOIN (
W M NATURAL,
W W INDEX (RDB$26), /* unique index Items (Manufacturer int, Name char) */

W C INDEX (RDB$PRIMARY3), /* (C)PCategories.ID */
W U INDEX (RDB$PRIMARY1) /* (C)PCategories.Unit_ID -> (U)Units.ID */
))

and "fast" is:

PLAN (
PC INDEX (RDB$PRIMARY3,RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories ID,
ParentID->ID */
)
PLAN JOIN (
PC INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC2 INDEX (RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories ParentID -> ID */
)
PLAN JOIN (
PC INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC2 INDEX (RDB$PRIMARY3), /* PCategories.ID */
PC3 INDEX (RDB$PRIMARY3,RDB$FOREIGN136) /* PCategories ParentID -> ID*/
)
PLAN SORT (JOIN (
W C NATURAL,
W U INDEX (RDB$PRIMARY1), /* (C)PCategories.Unit_ID->(U)Units.ID */
W W INDEX (RDB$FOREIGN22),/* (W)Items.PCategory -> (C)PCategories.ID */
W M INDEX (RDB$PRIMARY12) /* (W)Items.Manufacturer->(M)Manufacturers.ID, M
is a huge table */
))

Very bizarre...