I have a DB with a large table (1M records) and several small tables (~100 records).
I am running queries like this: select F1 from BigTable inner join SmallTable on BigTable.F2=SmallTable.F2 where SmallTable.F3=1 and BigTable.F4=2 That query takes 4 minutes to run and the query plan shows it is scanning SmallTable and then searching BigTable using an index that adds almost no value (it is a compound index with the only available column having the same value for over 99% of records, even though there is an index on F4 which would dramatically cut down the records it needs to look at). If I add the INDEXED BY clause: select F1 from BigTable INDEXED BY INDXBTF4 inner join SmallTable on BigTable.F2=SmallTable.F2 where SmallTable.F3=1 and BigTable.F4=2 In this case, query is subsecond and query plan shows it is first searching BigTable using the suggested index and then searching the small table using the same index it used in the first query. I have both run analyze and even re-indexed. I also deleted the indexes and re-added them and re-ran analyze. Results do not change.. Then I run this query (taking out the join): select F1 from BigTable where BigTable.F4=2 and it returns sub-second and query plan shows it correctly using INDXBTF4. Why would the original query use a different index on BigTable than this 3rd query? With such dramatic performance results, it is hard for me to understand why it is making a poor choice of indexes to use in the first query. I know I can simply add the INDEXED BY clause, but I am looking for a more general approach to improving this as we have a lot of queries that this can apply to. TIA, Denis
