Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))"
If you are often/constantly querying for only the last 7-7.999 days of data based on load_dttm, I would put that as the first column of the index since then you would be scanning a contiguous part rather than scanning 3 different parts of the composite index where actv_code = each of those three values, and then finding the rows that are recent based on the timestamp(tz?) field.
There is no need to cast the load_dttm field to a date in the query. The plain index on the field would be usable if you skipped that. In your example, you show creating the single column index but it isn't getting used because of the type cast. The second index is both partial, and multi-column. If your data statistics show that ((actv_code)::text = ANY ('{NAC,CAN,RSP,RCL}'::text[])) only 1% of the time, then it would certainly be helpful to have a partial index if those are the rows you want to find often and do so quickly. If the rows with those values for actv_code is more like 75% of the total rows, then there'd be no reason to make it partial IMO.
If you are often/constantly querying for only the last 7-7.999 days of data based on load_dttm, I would put that as the first column of the index since then you would be scanning a contiguous part rather than scanning 3 different parts of the composite index where actv_code = each of those three values, and then finding the rows that are recent based on the timestamp(tz?) field.
