Hi Philip!

My GUESS is that the chosen plan is due to your unusual way of joining. For an index to be used, it has to be possible to know the value to look for at the time it gets into the plan, and

FROM <table> A
LEFT JOIN <table> B on B.<Field> = A.<Field>

is very different from

FROM <table> A
LEFT JOIN <table> B on B.<Field> = <SubselectInvolvingProcedure>

I would expect

FROM <table> A
LEFT JOIN <StoredProcedure> on <Whatever>
LEFT JOIN <table> B on B.<Field> = <OutputFieldFromStoredProcedure>

to get a considerably better plan, but that would of course not get you the result you want.

I'm not saying that it is impossible to 'optimize the optimizer' so that it could get a better plan for cases like yours (I know nothing about that), just that your query is rather unusual.

I'd say you've solved your problem in one out of two-and-a-half possible ways. The 'half' part is using WITH instead of a derived table (they are just two ways to do the same), the other is to modify your stored procedures so that they return one additional field 'Preferred' for the preferred record and use

FROM <table> A
LEFT JOIN <StoredProcedure> on <Whatever> and Preferred = 1
LEFT JOIN <table> B on B.<Field> = <OutputFieldFromStoredProcedure>

I don't know why attempt 1 and 2 produce a suboptimal plan. Though, theoretically, your select could be both

FROM <table> A
LEFT JOIN <table> B on B.<Field> = (select 1 from SP(A.Field))

and

FROM <table> A
LEFT JOIN <table> B on B.<Field> = (select 1 from SP(A.Field, B.Field))

The first of these could theoretically use an index, the second couldn't. My guess is that it is so complex for the optimizer to cover all possible situations, that it hasn't been implemented.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of unordained
Sent: 16. oktober 2008 17:34
To: firebird-support@yahoogroups.com
Subject: [firebird-support] FB2.1.1 optimizer left join to selectable stored procedure

For some search results, I want to simplify the database model: when showing
people, only show one name, one address, etc. even though the database supports
several of each for each person. I built a couple of stored procedures to help
with this; given a person id and what my purpose is, they return the row id of
the most relevant entry, so I can join to it directly.

I get different optimizer results though depending on how I build this, and I'd
like to know what the general rule is. It seems that if FB can *see* that it's
joining using a return value from a selectable stored procedure, it refuses to
use an index for the join, and is choosing to do a full table scan ("NATURAL").
Performance-wise, that's really really bad.

(Hard-coded values are just there to simplify the query, to ignore how I'm
picking people to display -- it doesn't seem relevant to this issue.)

Attempt 1:

select bt_entities.id,
best_address.addr_display as primary_address,
best_phone.phone_display as primary_phone,
best_ssn.ident_block as primary_ssn,
best_taxid.ident_block as primary_taxid
from bt_entities
left join bt_entity_contacts best_address on best_address.id = (select first 1
contact_id from bt_prc_best_contact(37927, (select id from
bt_entity_contact_purpose_types where code = 'NORM'), (select id from
bt_entity_contact_types where code = 'MAIL')))
left join bt_entity_contacts best_phone on best_phone.id = (select first 1
contact_id from bt_prc_best_contact(37927, (select id from
bt_entity_contact_purpose_types where code = 'NORM'), (select id from
bt_entity_contact_types where code = 'PHONE')))
left join bt_entity_identities best_ssn on best_ssn.id = (select first 1
identity_id from bt_prc_best_identity(37927, (select id from
bt_entity_identity_types where code = 'SSN')))
left join bt_entity_identities best_taxid on best_taxid.id = (select first 1
identity_id from bt_prc_best_identity(37927, (select id from
bt_entity_identity_types where code = 'TAXID')))
where bt_entities.id = 37927;

The relevant part of the PLAN (ignoring the parts generated for the selectable
SP's which are in fact using indices internally correctly):

PLAN JOIN (JOIN (JOIN (JOIN (BT_ENTITIES INDEX (BT_PK_ENTS), BEST_ADDRESS
NATURAL), BEST_PHONE NATURAL), BEST_SSN NATURAL), BEST_TAXID NATURAL)

I'm joining to best_address, best_phone, etc. by a primary key, with a
selectivity of 0.000014 at the moment. It seems like a pretty obvious pick to
me. So I thought maybe the join was what confused it, but it looks like it's
the '=', in general; even a direct sub-select doesn't get optimized to use the
primary key index, as demonstrated below:

Attempt 2: (stripped down for testing)

select bt_entities.id,
(select best_address.addr_display from bt_entity_contacts best_address where
best_address.id = (select contact_id from bt_prc_best_contact(37927, 2, 3))) as
primary_address
from bt_entities
where bt_entities.id = 37927;

PLAN (BEST_ADDRESS NATURAL)
PLAN (BT_ENTITIES INDEX (BT_PK_ENTS))

I can, however, confuse it into working correctly, as follows:

Attempt 3:

select bt_entities.id,
best_address.addr_display as primary_address,
best_phone.phone_display as primary_phone,
best_ssn.ident_block as primary_ssn,
best_taxid.ident_block as primary_taxid
from
(
select 37927 entity_id,
(select first 1 contact_id from bt_prc_best_contact(37927, (select id from
bt_entity_contact_purpose_types where code = 'NORM'), (select id from
bt_entity_contact_types where code = 'MAIL'))) as best_address_id,
(select first 1 contact_id from bt_prc_best_contact(37927, (select id from
bt_entity_contact_purpose_types where code = 'NORM'), (select id from
bt_entity_contact_types where code = 'PHONE'))) as best_phone_id,
(select first 1 identity_id from bt_prc_best_identity(37927, (select id from
bt_entity_identity_types where code = 'SSN'))) as best_ssn_id,
(select first 1 identity_id from bt_prc_best_identity(37927, (select id from
bt_entity_identity_types where code = 'TAXID'))) as best_taxid_id
from rdb$database
) as x
left join bt_entities on bt_entities.id = x.entity_id
left join bt_entity_contacts best_address on best_address.id =
x.best_address_id
left join bt_entity_contacts best_phone on best_phone.id = x.best_phone_id
left join bt_entity_identities best_ssn on best_ssn.id = x.best_ssn_id
left join bt_entity_identities best_taxid on best_taxid.id = x.best_taxid_id;

Again, the relevant part of the plan (the rest, for the SP, is the same):

PLAN JOIN (JOIN (JOIN (JOIN (JOIN ((X RDB$DATABASE NATURAL), BT_ENTITIES INDEX
(BT_PK_ENTS), BEST_ADDRESS INDEX (BT_PK_ENTCONS)), BEST_PHONE INDEX
(BT_PK_ENTCONS)), BEST_SSN INDEX (BT_PK_ENTIDENTS)), BEST_TAXID INDEX
(BT_PK_ENTIDENTS)))

Here, it's doing exactly what I would expect. Use the obvious primary key
index, don't do a full table scan on each of those related tables.

I verified that the SP returns an 'integer' data type; the optimizer does the
same thing whether I use a "first 1" or not; it also doesn't improve if I
explicitly cast the return value of the sub-select to integer; and as
demonstrated, it doesn't seem to be about the "left join" itself so much as the
equality predicate.

Any clue why it behaves differently? I have a query that works now, I think,
but it worries me a little that I don't know why I had to change it.

Environment: FB2.1.1 SuperServer on Win32

Thanks!

-Philip