Using varchar_pattern_ops in a multi-column index in Postgres
The two-column btree index will help with the like 'foo%' query, but probably not dramatically so.  It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table.  The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the logical end of the index.  At each entry it will test the LIKE condition.  For the ones that pass that condition, it will check the visibility map to see if the table page holding that tuple is all visible.  If it is all visible, it will increment the counter and move on.  If not, it will have to visit the table page to see if the tuple is visible.

How much of a help this will be is hard to predict, as it depends on the size of the index, the table, your RAM, what mix of queries you are running (which changes which kind of data is likely to be found in the cache), among other things.

It will not help with the `like '%foobar' query.  From the outside looking in, there is no reason it couldn't help in the same way.  But PostgreSQL's index machinery just hasn't been made clever enough (yet) to recognize and implement that possibility.