I am still using Firebird 2.5 and for same query my plan looks like this

PLAN JOIN (O NATURAL, P INDEX (PK_PARTNERS))

according to your plan I need to switch to firebird 3 as soon as possible.

Best regards,

Djordje Radovanovic

From: mailto:firebird-support@yahoogroups.com
Sent: Monday, September 12, 2016 9:49 AM
To: firebird-support@yahoogroups.com
Subject: Re: Re: [firebird-support] Optimizer request

>
> 11.09.2016 10:29, 'Djordje Radovanovic' softsistem@... [firebird-support]
> wrote:
> > This time instead of 10000 reads of orders and 10000 reads of partners I
> > received exactly
> > what I was asking for.
>

Hi,

i do not know what server do you use but if i run simple query on FB3
select
o.*
from
partners p
inner join orders o ON p.partid = o.partid
WHERE
p.country = 'Spain';

on non propagated with data tables, plan is:

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))

but if i fill tables with test data and UPDATE INDEX STATISTICS
i got plan

PLAN JOIN (P INDEX (PARTNERS_IDX1), O INDEX (FK_ORDERS_PARTNERS))

then i suppose your real problem is your index statistics or selectivity of some values in joined fields

regards,
karol Bieniaszewski




[Non-text portions of this message have been removed]