After changing the sql statement to :

select coalesce(sum(gc.qty_nu),0)
from stkgrnchild gc
join stkmaster sm
on gc.stkmasterrowid_bi = sm.rowid_bi || 0
where sm.stkid_vc = '38338'

It indeed improve the performance and plan used has been changed to UNQ1_STKMASTER. The execute time was 63ms compare to 35s.

PLAN JOIN (SM INDEX (UNQ1_STKMASTER), GC INDEX (FK_STKGRNCHILD_STKMAS))


Thank you

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>
> >Good afternoon!
>
> Good morning, Wesly!
>
> >I have 2 tables stkmaster (50k records) and stkgrnchild (180k records) where I want to get
> >total quantity received particular stock id.
> >
> >The sql statement is :
> >
> > select coalesce(sum(gc.qty_nu),0)
> > from stkgrnchild gc
> > join stkmaster sm
> > on gc.stkmasterrowid_bi = sm.rowid_bi
> > where sm.stkid_vc = '38338'
> >
> >PLAN JOIN (GC NATURAL, SM INDEX (PK_STKMASTER))
>
> Interesting indexes/FKs (modified by me to make things easier to read):
>
> >FK_STKGRNCHILD_STKMAS ON STKGRNCHILD (STKMASTERROWID_BI)
> >UNQ1_STKMASTER ON STKMASTER (STKID_VC, COMPANYROWID_BI)
> >PK_STKMASTER ON STKMASTER (ROWID_BI)
> >
> >Any help to improve the performance is greatly appreciated.
>
> Your code seems OK to me, the optimizer expects it to be quicker to go natural on GC and then use the primary key for SM, than to use UNQ1_STKMASTER on SM and then FK_STCGRNCHILD_STKMAS on GC. I assume that SM.STKID_VC is not very selective and that GC.STKMASTERROWID_BI isn't all too selective either.
>
> However, Firebird doesn't have histograms yet, so it knows nothing about whether this plan would be the good for '38338' or if the alternative would be better. Try forcing Firebird and see whether things work quicker or not (replace +0 with ||'' if rowid_bi is not a number):
>
> select coalesce(sum(gc.qty_nu),0)
> from stkgrnchild gc
> join stkmaster sm
> on gc.stkmasterrowid_bi = sm.rowid_bi+0
> where sm.stkid_vc = '38338'
>
> HTH,
> Set
>