Help: slow SQL query performance using date range

Hi there, I'd appreciate some advice on getting the following SQL to run faster. As it stands the sql below takes around 2 minutes to return 36 rows, but if the two date range filters are commented out the query takes 1-2 seconds and returns 123 rows, even if just one of the date filters (any one) is commented out the query takes 1-2 seconds, but as soon as both are used the optimizer seems to get screwed over which index to use. In my mind, the main index join (using cnhldgid_ref) filters the subset down to around 123 rows - so the remaining filters in the query should not need to use any indexes. BTW, Informix Online is v7.23.UC1 and update statistics (high) has been run on the table. The developer is now looking to run a first query to get the 123 rows to a temp table and then do a following sql on that. # of rows in tab_a = 280,000 # of rows in tab_b = 1,200,000 QUERY: ------ select tab_b.* from tab_b, tab_a where tab_a.cnhldgid_ref = tab_b.cnhldgid_ref and tab_b.due_date >= '17/02/1997' and tab_b.due_date <= '16/02/1998' and tab_a.fk_ref = 298516 and tab_b.status_ref = 99 Estimated Cost: 7 Estimated # of Rows Returned: 1 1) new067.tab_b: INDEX PATH Filters: new067.tab_b.status_ref = 99 (1) Index Keys: due_date Lower Index Filter: new067.tab_b.due_date >= 17/02/1997 Upper Index Filter: new067.tab_b.due_date <= 16/02/1998 2) new067.tab_a: INDEX PATH Filters: new067.tab_a.fk_ref = 298516 (1) Index Keys: cnhldgid_ref Lower Index Filter: new067.tab_a.cnhldgid_ref = new067.tab_b.cnhldgid_ref -- Simon Barber