"Is that because phoenix optimizer figures that having all the columns in
index does not add value over doing a self join with main table for the
inner join query" -- Correct. A count(*) query would have to read the entire table. There's no point in using the index table for this.
"In that sense, is it a bad practice to add all columns to secondary index?" -- Yes, you should only add columns to an index that are commonly used together. Adding all columns to a secondary index is pointless.