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

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links