Rick,

Please do not top-post and trim your replies (don't completely re-quote).


Sean

> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of Rick Debay
> Sent: Wednesday, April 24, 2013 2:17 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] SV: Forcing optimizer to use index
>
> The problem appeared when on a small test box the cache was reduced from
> 8192 8KB pages to 4096 pages in order to reduce memory usage. The query is
> extremely fast when run a second time, so I assume a large number of pages
> have to be moved from disk to cache for each query.
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
> Sent: Tuesday, April 23, 2013 1:32 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] SV: Forcing optimizer to use index
>
> >I have a query that needs to use an index that the optimizer isn't
> >using. How can I get it to add the index to the query plan?
> >The current plan is
> >
> >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
> >(I_PBM_CLAIM_NDC), AC RGM SA INDEX
> (PK_ACT_CHC_PHARM_SUBACCT), AC RGM C
> >INDEX (PK_CHC), AC RGM PRG INDEX (PK_CRM_PHARM_RPL_GRP), AC
> RGM RG
> >INDEX
> >(PK_CRM_RPL_GRP)))
> >
> >And it needs to be
> >
> >PLAN SORT (JOIN (AC D INDEX (I_MDDBDRUG_GPI), AC CA C INDEX
> >(I_PBM_CLAIM_NDC, I_PBMCLAIM_DATESBM), AC RGM SA INDEX
> >(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC RGM
> PRG INDEX
> >(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX (PK_CRM_RPL_GRP)))
> >
> >Without I_PBMCLAIM_DATESBM instead of scanning a few months of data
> >it's scanning a few years.
>
> Have you timed your query with the plans hardcoded to ascertain that it
> really is quicker using your preferred plan, Rick? I doubt you'll get much help
> from this list without including the query itself, the index definitions with a
> little bit of additional information about their selectivity and tell us which
> Firebird version you're using. You may be able to reduce the query (and
> highlight your problem) by eliminating the non-important tuples of your
> query (you definitely need AC D and AC CA C, but the rest may not matter for
> your problem), but it might also be that someone will spot a better way to
> write your query if you include the entire text.
>
> One thing regarding your desired plan: I think (don't know, it is just a hunch)
> that using several indexes for a table is most useful if it is the first table in the
> plan, so it might be better to have a plan like:
>
> PLAN SORT (JOIN (AC CA C INDEX(I_PBMCLAIM_DATESBM, <possibly
> another index>), AC D INDEX (<index linking to AC CA C>), AC RGM SA
> INDEX(PK_ACT_CHC_PHARM_SUBACCT), AC RGM C INDEX (PK_CHC), AC
> RGM PRG INDEX(PK_CRM_PHARM_RPL_GRP), AC RGM RG INDEX
> (PK_CRM_RPL_GRP)))
>
> Set
>
> ------------------------------------