informix - Query is not using Index

Question

Following is my query with complete explain plan and when I taking the explain plan, its giving sequential scan on ach_batches.ach_batch_date where as the index is present on the table's mentioned column. Further, the problem is enough IO peaks observed on DB server otherwise query performance is not bad.

QUERY: (OPTIMIZATION TIMESTAMP: 01-23-2021 04:23:45)        (FIRST_ROWS OPTIMIZATION)

------
SELECT
    ach_batches.ach_batch_sr_no,
    ach_batches.ach_origin_id,
    ach_batches.odfi_routing_no,
    ach_batches.ach_batch_date,
    ach_batches.company_name,
    ach_batches.ach_tot_dr_amount,
    ach_batches.ach_tot_cr_amount,
    ach_batch_details.ach_batch_rec_no,
    ach_batch_details.trans_id,
    ach_batch_details.ach_trans_code,
    ach_batch_details.rdfi_routing_no,
    ach_batch_details.dfi_account_no,
    ach_batch_details.amount,
    ach_batch_details.receiver_id,
    ach_batch_details.receiver_name,
    ach_batch_details.ach_bat_ret_code,
    transaction_types.trans_type_abrv,
    b_settle_statuses.name
FROM
    ( ( ach_batches ach_batches
INNER JOIN ach_batch_details ach_batch_details
ON
    ach_batches.ach_batch_sr_no=ach_batch_details.ach_batch_sr_no )
INNER JOIN transaction_types transaction_types
ON
    ach_batch_details.trans_type=transaction_types.trans_type )
INNER JOIN b_settle_statuses b_settle_statuses
ON
    ach_batch_details.settle_status=b_settle_statuses.status
WHERE
    (
        (
            ach_batches.ach_batch_date >= '01/09/2021'
        )
    )
AND
    (
        (
            ach_batches.ach_batch_date <= '01/10/2021'
        )
    )
ORDER BY
    ach_batches.ach_batch_sr_no,
    ach_batch_details.ach_batch_rec_no

Estimated Cost: 46114
Estimated # of Rows Returned: 1
Temporary Files Required For: Order By  

  1) hfarooq.ach_batches: SEQUENTIAL SCAN

        Filters: (hfarooq.ach_batches.ach_batch_date >= 01/09/2021 AND hfarooq.ach_batches.ach_batch_date <= 01/10/2021 ) 

  2) hfarooq.ach_batch_details: INDEX PATH

    (1) Index Name: informix. 122_103
        Index Keys: ach_batch_sr_no ach_batch_rec_no   (Serial, fragments: ALL)
        Lower Index Filter: hfarooq.ach_batches.ach_batch_sr_no = hfarooq.ach_batch_details.ach_batch_sr_no 
NESTED LOOP JOIN

  3) hfarooq.transaction_types: INDEX PATH

    (1) Index Name: mcp. 323_850
        Index Keys: trans_type   (Serial, fragments: ALL)
        Lower Index Filter: hfarooq.ach_batch_details.trans_type = hfarooq.transaction_types.trans_type 
NESTED LOOP JOIN

  4) hfarooq.b_settle_statuses: INDEX PATH

    (1) Index Name: mcp. 2420_8748
        Index Keys: status   (Serial, fragments: ALL)
        Lower Index Filter: hfarooq.ach_batch_details.settle_status = hfarooq.b_settle_statuses.status 
NESTED LOOP JOIN

Table's index:

question from:https://stackoverflow.com/questions/66065545/query-is-not-using-index

Answer 1

Waitting for answers