Interbase query optimiser problem

[Robert Klinkenberg]

Hello,

I have the following two queries that perform very differently.
Anyone who knows why Interbase won't use all the indexes that are available.

Select MasterItems.*
from Items, ParentItems, Items MasterItems
where Items.event_ID=1068
and ParentItems.Item_ID=Items.Item_ID
and ParentItems.Parent_ID=MasterItems.Item_ID

takes +/- 20 seconds.

Select MasterItems.*
from ParentItems, Items MasterItems, items
where Items.event_ID=1068
and ParentItems.Item_ID=Items.Item_ID
and ParentItems.Parent_ID=MasterItems.Item_ID

takes 30 msec according to IBAdmin

The only difference between the two is that the order of tables in the SQL
has been changed.
This results in two different plans.
One uses the primary index of ParentItems (both Item_ID and Parent_ID
fields) and the Index defined on Event_ID in the items table. It performs a
table scan on Items and that is pretty costly hence 20 seconds.

The other one uses the primary index of the Items table (on Item_ID) twice
and performs a tablescan on ParentItems (and that table is not yet
completely filled hence the 30 msec for this one).

How come that these two queries performs so completely different?
And how can I force the query to use an index instead of a tablescan?

For what it's worth I'm using Firebird 1.0.0.338 beta 2 as a server for now.

Robert Klinkenberg

[Dmitry Kuzmenko]

Hello, Robert!

Robert Klinkenberg wrote:

> The only difference between the two is that the order of tables in the SQL
> has been changed.
> This results in two different plans.

I suggest you to rebuild index statistics. You can take
gtools.zip toolset from www.webinterbase.com
(http://www.webinterbase.com/Downloads/gtools.zip)

and use GIDX to do this automatically (and, maybe periodically).

--
Dmitry Kuzmenko, Epsylon Technologies.
www.webinterbase.com

[rsch...@mers.com]

A few InterBAse versions ago, there was a problem with the optimizer where
it would only look at half of the possible solutions to your query. By
reversing the order of the tables it would look at the other half.
Sometimes it was better sometimes it was worse (some times much worse).

hth

Rob Schieck
MER Systems Inc.

On Sat, 20 Oct 2001, Robert Klinkenberg wrote:

> Hello,
>
> I have the following two queries that perform very differently.
> Anyone who knows why Interbase won't use all the indexes that are available.
>
> Select MasterItems.*
> from Items, ParentItems, Items MasterItems
> where Items.event_ID=1068
> and ParentItems.Item_ID=Items.Item_ID
> and ParentItems.Parent_ID=MasterItems.Item_ID
>
> takes +/- 20 seconds.
>
> Select MasterItems.*
> from ParentItems, Items MasterItems, items
> where Items.event_ID=1068
> and ParentItems.Item_ID=Items.Item_ID
> and ParentItems.Parent_ID=MasterItems.Item_ID
>
> takes 30 msec according to IBAdmin
>

> The only difference between the two is that the order of tables in the SQL
> has been changed.
> This results in two different plans.

> One uses the primary index of ParentItems (both Item_ID and Parent_ID
> fields) and the Index defined on Event_ID in the items table. It performs a
> table scan on Items and that is pretty costly hence 20 seconds.
>
> The other one uses the primary index of the Items table (on Item_ID) twice
> and performs a tablescan on ParentItems (and that table is not yet
> completely filled hence the 30 msec for this one).
>
> How come that these two queries performs so completely different?
> And how can I force the query to use an index instead of a tablescan?
>
> For what it's worth I'm using Firebird 1.0.0.338 beta 2 as a server for now.
>
> Robert Klinkenberg

> _______________________________________________
> Interbase mailing list
> Inte...@mers.com
> http://mers.com/mailman/listinfo/interbase
> InterBase Search Engine - http://www.mers.com/searchsite.html
>

[Ann W. Harrison]

At 02:29 PM 10/20/2001 +0200, Robert Klinkenberg wrote:

>I have the following two queries that perform very differently.

> .....

>from Items, ParentItems, Items MasterItems

> .....

>from ParentItems, Items MasterItems, items

I think that the problem is with the double use of
the Items table. My guess is that the two queries
would be equivalent if you qualified all three tables.

Select M.*
from Items I, ParentItems P, Items M
where I.event_ID=1068
and P.Item_ID=I.Item_ID
and P.Parent_ID = M.Item_ID

An earlier relational language I used required that
all tables and all column references be qualified.
It's not a bad rule.

Regards,

Ann
www.ibphoenix.com
We have answers.