hi,

> > PLAN JOIN (A INDEX (RDB$FOREIGN132),B INDEX (RDB$FOREIGN112))
> > takes 1.43sec
>
> Are there 2 PLANs returned by this query or is it just a copy-

paste mistake?

> Assuming 1 PLAN is returned, 1 index on A and 1 index on B is used.
> I've to guess on which fields, may be you could post more metadata

about the

> indexes used.
>
>

> In your case all those indexes are the problem, to many indexes

are used.

> May be you've defined to much indexes? or defined wrong indexes.
>

/* Foreign keys definition */
***RDB$FOREIGN112***
ALTER TABLE TB_INSPECT ADD CONSTRAINT FK_INSPECT_GROUP FOREIGN KEY
(INSPECTION_GROUP_ID) REFERENCES TB_INSPECT_GROUP
(TB_INSPECT_GROUP_ID) ON DELETE CASCADE ON UPDATE CASCADE;

***RDB$FOREIGN132***
ALTER TABLE TB_INSPECT ADD CONSTRAINT FK_INSPECT_MACHINES FOREIGN
KEY (MACH_ID) REFERENCES TB_MACHINES (TB_MACHINES_ID) ON UPDATE
CASCADE;


/* Indices definition */
***A LOT OF REPEATED*** //But helped me on other queries
CREATE INDEX IDX_TB_INSPECT_STATUS ON TB_INSPECT (STATUS);
***A LOT OF NULLS*** //
CREATE UNIQUE INDEX IDX_TB_INSPECT_WUP_DET ON TB_INSPECT
(WRITE_UP_DET_ID);


Indexes in TB_INSPECT

Index IDX_TB_INSPECT_STATUS (2)
Depth: 2, leaf buckets: 212, nodes: 286596
Average data length: 0.00, total dup: 286591, max dup:
272337
Fill distribution:
0 - 19% = 0
20 - 39% = 1
40 - 59% = 0
60 - 79% = 0
80 - 99% = 211

Index IDX_TB_INSPECT_WUP_DET (5)
Depth: 2, leaf buckets: 220, nodes: 286596
Average data length: 0.00, total dup: 220031, max dup:
220031
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 1
60 - 79% = 0
80 - 99% = 219

Index RDB$FOREIGN112 (3)
Depth: 2, leaf buckets: 220, nodes: 286596
Average data length: 0.00, total dup: 224185, max dup: 27
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 219

Indexes in TB_INSPECT_GROUP

Index RDB$FOREIGN132 (4)
Depth: 2, leaf buckets: 53, nodes: 70368
Average data length: 0.00, total dup: 63520, max dup: 47047
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 53

Again, I think the optimizer should go to statistics and determine
when to use an index and when not.

Thanks

Juan Jose.