Hi Sean!

Mitch' 'problem' is that he uses OR EXISTS(<subquery referencing fields in the outer select>)..., which forces Firebird to examine every record of Deal anyway and makes it impossible for Firebird to benefit from an index - well, at least impossible for Firebird 1.5, I haven't tried expression indexes with Firebird 2 yet, and don't know whether they could help him. If he has gazillions of records within DEAL, the EXISTS could be enough to make the query too slow and a stored procedure or union being his options (as far as I can think). Generally, I love EXISTS, but when it is one of the outermost conditions (read: OR without any AND outside), it does force a NATURAL scan. I'm sorry to say that I'd be shocked if your two latest suggestions below would change the plan to anything better.

Thanks to those that answered my uncertainty about UNION - I knew duplicates between what was being UNIONed were removed, I simply never checked whether duplicates within each SELECT part were removed or not.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: 14. mars 2008 05:33
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Plan Syntax

Mitch,

> > 1 - Do you have an index on Deal EditDate? (This could helps get

rid of

> > the NATURAL scan)
> >
> >
> I do. It was this index that I wanted to use.

What PLAN is generated if:

1 - you move the "and C.Id=(Select Max(C2.ID) from discussion C2 where
C2.deal_id=d.id)" criteria from the JOIN to the WHERE.

2 (or) - change the "and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)" criteria to "and C.Id=(Select Max(C2.ID) from
discussion C2 where C2.deal_id=d.id+0)" criteria

I found that the v1.x optimizer had a number of issues with choosing the
best index.

These 'tricks' might get the Deal EditDate index to come into play, and
thus reduce the number of tests which the other criteria would invoke.

> > 2 - You should create a DESCENDING compound index in Discussion,
> > combining Deal_ID + ID). The optimizer should choose this index to
> > resolve the MAX() criteria.
>
> I thought of that. However, there should be few rows per "Deal", so,
> not sure that would be a tremendous help, but is certainly worth a

try.

It think you would be very surprised, this particular index (the
DESCENDING index) would reduce the number of fetches per deal to 1 --
since the first indexed read for each Deal would yield the highest ID
value.

> > 3 - You should create compound indexes on Attachments and

Discussions

> > (combining DealID + CreateDate). The optimizer should choose these
> > indexes over the current approach of trying to create a bit map by
> > combining the separate indexes (FK_DISCUSSION_1/IDX_DISCUSSION_2 and
> > FK_ATTACHMENT_2/IDX_ATTACHMENT_1).
>
> another goo suggestion. Thanks, I'll try it.

I would be beneficial if you could make one change at a time and post
the PLAN and performance numbers, rather then doing all the changes in
one step.

It would give a better sense of the impact of each change.

Sean