Rick, Please do not top-post and trim your replies (don't completely re-quote).
Sean > -----Original Message----- > From: [email protected] [mailto:firebird- > [email protected]] On Behalf Of Rick Debay > Sent: Wednesday, April 24, 2013 2:17 PM > To: [email protected] > 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: [email protected] [mailto:firebird- > [email protected]] On Behalf Of Svein Erling Tysvær > Sent: Tuesday, April 23, 2013 1:32 PM > To: [email protected] > 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 > > ------------------------------------
