Re: Single column vs composite partial index

Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))"

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.