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