Execution plan shows 3 times less CPU cost but query now takes 4 times more time
Attached are 2 execution plans of a query - one is with index BITMAP_COACH_STATUS_FLAG and one is without the aforementioned index.
The execution time of query was 55 seconds. Using the execution plan, I created a BITMAP index mentioned above. The CPU cost went down from 383K to 96K.
Interestingly using the index, the CPU cost reduced 3-4 times (96K) but the execution time increased 5 times (4 minutes 47 seconds). When I used the NO_INDEX hint, the CPU cost again went up to 383K but the execution time went down to 55 seconds.
What should be done in such a scenario?